# Thread: sum cells in alternating sheets

1. I would like to sum a specific cell in alternating sheets, sheet2, 4, etc.

I can list the sheet names in my lead sheet, if necessary.
I've been playing around with using INDIRECT, but just can't get this right.

This works with the sheet names in AA, but I need the "4" to be dynamic based on a cell in the lead sheet.

=SUMPRODUCT(SUMIF(INDIRECT("'"&\$AA\$2:\$AA\$4&"'!P28" ),"<>"))

2. Why not use a formula such as

=Sheet2!A3+Sheet4!A3+Sheet6!A3+... ?

Or else, why not reorder the sheets so that the sheets you want to sum are adjacent? You could then use a formula such as

=SUM(Sheet2:Sheet16!A3)

3. I cannot reorder them because the user needs sheets in "pairs".
Sometimes there are 4 pairs, sometimes 6 pairs, etc.

I know how many pairs there are, obviously.

This works, but I need to be able to vary the "4" to be based on c1+1 of my lead sheet.
Sheet names are in the AA column of my lead sheet.

=SUMPRODUCT(SUMIF(INDIRECT("'"&\$AA\$2:\$AA\$4&"'!P28" ),"<>"))

[quote name='HansV' post='775295' date='14-May-2009 19:36']Why not use a formula such as

=Sheet2!A3+Sheet4!A3+Sheet6!A3+... ?

Or else, why not reorder the sheets so that the sheets you want to sum are adjacent? You could then use a formula such as

=SUM(Sheet2:Sheet16!A3)[/quote]

=Sheet2!P28+Sheet4!P28*(C1>1)+Sheet6!P28*(C1>2)+Sh eet8!P28*(C1>3) + ....

Steve

5. That's a good idea if I can't make the "4" in my previous post a variable: 1+C1)

Seems like a lot of wasted typing and (simple) calculating if I only need 3 sheets one time or 4 another...I'll have to allow for 20 or so in this application.

[quote name='sdckapr' post='775418' date='15-May-2009 18:40']How about something like:

=Sheet2!P28+Sheet4!P28*(C1>1)+Sheet6!P28*(C1>2)+Sh eet8!P28*(C1>3) + ....

Steve[/quote]

6. Another option may be much simpler especially if you must create 20 sums, by not trying to do it directly.

If you have a list of names in AA2 to whatever, why not use the INDIRECT function in AB to pull in the cell of interest from each sheet. Create it once in AB2 and copy it down the column.

Then you can sum the values in AB2:AB whatever using SUM and OFFSET and the value in C1...

I will leave the details to you since I am not completely sure I understand them all completely...

Steve

#### Posting Permissions

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