Sum Salaries from separate sheets - Sheet names in a dynamic array

Try this array formula**:

=SUM(IF(B4:D4="On",SUMIF(INDIRECT("'"&B3:D3&"'!B2"),">0")))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

<-- this works, but I can't use array formulas in the spreadsheet

You can't use array formulas or you don't want to use array formulas?

Without the use of intermediate calculations I don't think it can be done without an array formula.

Don't be afraid of array formulas!

I'll be going offline for several hours.

I'll get back to ya!

Here's what I came up with but it requires an intermediate calculation.

You need to compile a list of the sheet names to be included in the calculation and this list needs to be such that all the sheets that meet the condition have to be in a contiguous range.

For example...

Data Range

	A	B	C	D
3	Name:	James	Mary	Laura
4	On/Off:	On	Off	On
5
6
7
8
9
10	James
11	Laura
12	#NUM!
13	#NUM!
14	#NUM!
15	#NUM!

This array formula** entered in A10 and copied down to A15:

=INDEX($3:$3,SMALL(IF(B$4:D$4="On",COLUMN(B4:D4)),ROWS(A$10:A10)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Then, to get the sum across the sheets:

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&A10:INDEX(A10:A15,MATCH("zzzzz",A10:A15))&"'!B2"),"<>0")),0)

We have to pass only the sheet names to the INDIRECT function to form a valid reference otherwise INDIRECT would generate an error.

I don't see any advantage in using the array formula to generate the sheet names over using the array formula to get the sum.

Seems like an extra unnecessary step.