Results 1 to 10 of 10

Thread: GoogleDocs Spreadsheet Explosion

  1. #1

    GoogleDocs Spreadsheet Explosion



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I copied an Excel spreadsheet to GoogleDocs, and it blew up a couple of formulas because they're arrays, I suppose. On taking their direction using =ArrayFormula(FormulaHere), it didn't work.

    I am trying to COUNTIF between date1 and date2 in column C of multiple worksheets. My formula worked in Excel. Here it is.

    =SUMPRODUCT(--INDIRECT(E5&"!$d$3:$d$1000000")>=B4,--INDIRECT(E5&"!$d$3:$d$1000000")<=$B$2)

    Also, E5 contained a person's first name, where that first name is the Sheet name. I'd rather not type each formula individually.

    Any help is greatly appreciated. Have any of you done the GoogleDocs thang?

    Great forum, Ken. THANKS!!!

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Honestly, I haven't tried GoogleDocs beyond a very brief test when they first announced.

    I'm curious... not go all Microsofty on you, but have you tried the file on Windows Live SkyDrive? So far the only formula I've run into that doesn't work is =CELL. (Based on tests, INDIRECT does work.)

    It may be worth checking out, unless there are features of GoogleDocs that you need which can't be replicated in SkyDrive/WebApp.

    And thanks for the kind words too. It's great to see you here!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Unfortunately, this isn't my decision. GoogleDocs is already in use with the company.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Fair enough... that would be a good reason to be seeking a solution then.

    So you tried this then?
    =SUMPRODUCT(ArrayFormula(--INDIRECT(E5&"!$d$3:$d$1000000")>=B4),ArrayFormula(--INDIRECT(E5&"!$d$3:$d$1000000")<=$B$2))

    And to be up front here, I'm TOTALLY guessing...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Hi Anne. Couple of thoughts:

    You say you're using this in excel: =SUMPRODUCT(--INDIRECT(E5&"!$d$3:$d$1000000")>=B4,--INDIRECT(E5&"!$d$3:$d$1000000")<=$B$2)
    ...but for me this always returns zero. Are you actually using this (i.e. brackets separating the - - and the INDIRECT):
    =SUMPRODUCT(--(INDIRECT(E5&"!$d$3:$d$1000000")>=B4(,--(INDIRECT(E5&"!$d$3:$d$1000000")<=$B$2))
    ...or am I missing something?

    Note this can also be written like this, which might be slightly less confusing:
    =SUMPRODUCT((INDIRECT(E5&"!$d$3:$d$10")>=B4)*(INDIRECT(E5&"!$d$3:$d$10")<=$B$2))
    ...that is, you only need the double unary if you are NOT multiplying an array by something...so if you multiply the two arrays together in the first SUMPRODUCT argument you don't need them. See http://www.excelhero.com/blog/2010/0...umproduct.html for more on this.

    On google docs, it seems that their implementation of SUMPRODUCT DOES NOT require a double unary to convert an array of trues or falses into ones and zeros, and in fact it is probably the use of the double unary operator that makes the formula bomb out. This should work fine:
    =SUMPRODUCT(ARRAYFORMULA(INDIRECT(E5&"!$d$3:$d$10")>=B4),ARRAYFORMULA(INDIRECT(E5&"!$d$3:$d$10")<=B2))

  6. #6
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Another thought: while this WON'T work in goggle docs as far as I can tell, you don't need to use indirect for this task in excel. You could use
    =SUMPRODUCT((CHOOSE(E6,data1,data2)>=B4)*(CHOOSE(Sheet1!E6,data1,data2)<=$B$2))
    ...where E6 converts your 'name' to a number, and 'data1', 'data2', through to 'datan' are defined names pointing to the columns in each spreadsheet that you want to evaluate.

  7. #7
    Thanks for your reply. Just now got a chance to look. You say I can choose CHOOSE, but the indirect points to a cell that contains the names of the worksheets. I'm gonna try your version of the formula. I got the -- from some website. Not my choice. I still don't understand it!

  8. #8
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Hi Anne. Just to clarify, note that the CHOOSE example WILL NOT work in Google Docs because the Google Docs implementation of that function does not return an array. I was just pointing out that it's an interesting way to do what you want in MS Excel.

    However, my revamped SUMPRODUCT example
    Code:
    =SUMPRODUCT(ARRAYFORMULA(INDIRECT(E5&"!$d$3:$d$10"  )>=B4),ARRAYFORMULA(INDIRECT(E5&"!$d$3:$d$10")<=B2  ))
    works fine in Google spreadsheet as far as I can tell.
    Last edited by Zack Barresse; 2011-03-29 at 06:07 PM. Reason: Changed CODE tag enclosures

  9. #9
    I can't get it to work. Sorry. I can share the workbook with someone if they PM me or email me (anne.troy at gmail) their email addy. Thanks!

  10. #10
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Have sent you an email.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •