Yogi Anand, D.Eng, P.E. Google Spreadsheet www.energyefficientbuild.com
user jhizon said:
Array calculations in an Indirect function
Some of you may recognize this formula as this is a follow up to a question I had yesterday.
I have the formula:
=SUMPRODUCT(COUNTIF(INDIRECT("
Here's what it does. Within the range F2:F18 is a list of sheet names.
This formula counts the number of times "Yes" appears in cell B3 of each sheet listed in that range.
Apparently, using an array in the indirect function is a problem. Is there any other workaround for gsheets aside from doing something like:
=ArrayFormula( SUM( { INDIRECT( ʺ'ʺ & $F$2 & ʺ'!B3ʺ ); INDIRECT( ʺ'ʺ & $F$3 & ʺ'!B3ʺ ); INDIRECT( ʺ'ʺ & $F$4 & ʺ'!B3ʺ ); … etc etc… INDIRECT( ʺ'ʺ & $F$18 & ʺ'!B3ʺ ) } = ʺYesʺ ) )
The code is functional but obviously not an ideal alternative.
---------------------------------------------------------
following is a solution to the problem
I cant see this example sheet, doesnt show up, my connection with google docs works so I though maybe you removed it or maybe it just isnt working atm. Anyway, im intrested in seeing it, could you please mail me the solution if it doesnt pop up here again?
ReplyDeleteCheers
B
Now it worked!
DeleteHi Bjorn:
ReplyDeleteSorry about that ... I can not load that spreadsheet by itself either. I hope to hear from Google guides/engineers soon ... otherwise I will look into building the spreadsheet from scratch.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com