GoogleDocs Spreadsheet Explosion

[Anne Troy]

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!!!

[Ken Puls]

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!

[Anne Troy]

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

[Ken Puls]

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...

[JeffreyWeir]

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))

[JeffreyWeir]

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.

[Anne Troy]

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!

[JeffreyWeir]

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

=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.

[Anne Troy]

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!

[JeffreyWeir]

Have sent you an email.