Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •