sum if across multiple sheets

[paullie d]

I have an array formula that I want to work over multiple sheets but the following formula doesn't work. Am I misstating how excel looks at multiple sheets? I want to look down the same column (A5 to A80) across multiple sheets (sheet names are 1,2,3,4,5,6,7,8,9,10,11,12,13). If the number is 99 in A5 to A80, sum H5:H80 across the 13 sheets and give me the result. Here's the incorrect array formula:
{=SUM(IF('1:13'!A5:A80=99,'1:13'!H5:H80,""))}

 

[Aladin Akyurek]

3-D references cannot be used in array formulas.

 

[paullie d]

On 2002-03-25 15:26, Aladin Akyurek wrote:
3-D references cannot be used in array formulas.

Then how can I set this up to work?

 

[Aladin Akyurek]

On 2002-03-25 15:29, paullie d wrote:

On 2002-03-25 15:26, Aladin Akyurek wrote:
3-D references cannot be used in array formulas.

Then how can I set this up to work?

One way:

In the main sheet, enter sheet names 1, 2, etc in A from A2 on.

In B1 enter: 99 [ your condition/criterion ]

In B2 enter and copy down as far as needed:

=SUMIF(INDIRECT(A2&"!A5:A80"),B6,INDIRECT(A2&"!H5:H80"))

Then apply SUM from B2 to the last cell in B.

Note. There is a recent thread in which a macro has been described, which can list all the sheet names in your WB in the desired range.

Aladin