# Thread: Selective SUM Multiple Sheet (Excel 2003)

1. ## Selective SUM Multiple Sheet (Excel 2003)

I have workbook with 50 different worksheets named 1,2,3 ....49,50. In cell A1 of each sheet, there is a different dollar amount. I can use sum(1:50!A1) to sum up all A1 cells. Is there a formula to selectively sum these sheets, something like: sum(1!, 3!, 4! A1) ? Better still, can the 1!, 3! be a reference so I can change the number in another list ?

2. ## Re: Selective SUM Multiple Sheet (Excel 2003)

Try, array entered,

=SUMPRODUCT(N(INDIRECT("'"&\$B\$1:\$B\$4&"'!A1")))

Where B1:B4 houses sheet names and no empty cells in the range

3. ## Re: Selective SUM Multiple Sheet (Excel 2003)

Maxflia, thanks ! FYI, as I need to skip sheets, I just created a sheet called 0, and then fill the reference cells with 0. Or is there a better way to do this ?

4. ## Re: Selective SUM Multiple Sheet (Excel 2003)

Say that you want to sum A1 for the sheets 3, 12 and 37.
Enter 3 in B1, 12 in B2 and 37 in B3.
Maxflia's formula becomes
=SUMPRODUCT(N(INDIRECT("'"&\$B\$1:\$B\$3&"'!A1")))

5. ## Re: Selective SUM Multiple Sheet (Excel 2003)

Hans, but my problem is empty cells in the list, say if I leave B1 empty, then the formula would not work.

6. ## Re: Selective SUM Multiple Sheet (Excel 2003)

I don't understand why you would leave one of those cells empty, but this should work:
<code>
=SUM(IF(B1:B4<>"",N(INDIRECT("'"&B1:B4&"'!A1"))))
</code>
again entered as an array formula.

#### Posting Permissions

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