Thanks:  0
Likes:  0

# Thread: sum if across multiple sheets

1. 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,""))}

2. 3-D references cannot be used in array formulas.

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•