Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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]

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about something like:

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

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
  •