Tuesday, December 6, 2011

yogi_Workaround For Array Calculations In Indirect Function

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