Results 1 to 7 of 7

Thread: SumIf Across Multiple Worksheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SumIf Across Multiple Worksheets

    Hello Everyone, wondering if someone might be able to help me out with a formula I am having trouble with. I have researched the formula extensively on-line and can't seem to get it to work. Basically, I am trying to use a sumifs formula with multiple criteria that sums across multiple worksheets using the indirect formula (see example below). I believe it can be done, but just cant seem to get it to work. Any help is much appreciated!


    F G H I J K L M N O P Q R S T U V W X
    1 Sum Range Criteria 1 Criteria 2
    2 Sheet Reference Column Row Column Row Column Row
    3 Sheet1 I 9 f 9 I 8
    4 Sheet2 X 15 f 15 X 8
    5
    6
    7 Month
    8 Cost Category 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
    9 a 18 4 1 19 20 15 3 19 1 10 3 5 3 15 16 3
    10 b 3 19 17 7 13 10 13 12 20 10 4 3 13 7 20 12
    11 c 17 16 4 3 9 16 11 18 13 12 14 13 0 6 10 6
    12 d 4 2 16 19 16 2 1 15 0 4 20 13 2 13 18 19
    13 e 6 12 4 15 18 20 1 13 17 6 8 1 4 15 15 0
    14 f 17 2 2 5 4 18 7 10 10 2 20 1 14 0 4 2
    15 g 17 4 12 18 10 0 7 19 14 15 11 15 15 19 9 14
    16
    17 RESULT #VALUE!
    Sheet1

    Worksheet Formulas
    Cell Formula
    G17 =SUMPRODUCT(SUMIFS(INDIRECT("'"&$F$3:$F$4&"'!"&$I$3&$J$3&":"&$I$4&$J$4),INDIRECT("'"&$F$3:$F$4&"'!"&$L$3&$M$3&":"&$L$4&$M$4),$F$11,INDIRECT("'"&$F$3:$F$4&"'!"&$O$3&$P$3&":"&$O$4&$P$4),">="&0,INDIRECT("'"&$F$3:$F$4&"'!"&$O$3&$P$3&":"&$O$4&$P$4),"<"&12))

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,730
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: SumIf Across Multiple Worksheets

    You have several issues. First, for SUMIFS, the ranges must be the same size and shape. You appear to be using a SUMPRODUCT type construct where you're comparing values against the left column, as well as across the top row of headers. This won't work either, since SUMPRODUCT works on 2-dimensional arrays, and when you throw in multiple sheets, you have a 3-dimensional array. Instead, you have to come up with a way to collapse one of the dimensions into a single value.

    In this example, I use SUBTOTAL to sum up 12 columns into a single value. I use MATCH to find the row (sadly, the row headers must be the same on all sheets, in the same order). Put this formula in G17:

    =SUMPRODUCT(SUBTOTAL(9,OFFSET(INDIRECT(F3:F4&"!"&I3&J3),MATCH(F11,INDIRECT(L3&M3&":"&L4&M4),0)-1,0,1,12)))

    That seems to do what you want, but it might be hard to adapt to your workbook if your conditions change at all.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SumIf Across Multiple Worksheets

    Quote Originally Posted by Eric W View Post
    You have several issues. First, for SUMIFS, the ranges must be the same size and shape. You appear to be using a SUMPRODUCT type construct where you're comparing values against the left column, as well as across the top row of headers. This won't work either, since SUMPRODUCT works on 2-dimensional arrays, and when you throw in multiple sheets, you have a 3-dimensional array. Instead, you have to come up with a way to collapse one of the dimensions into a single value.

    In this example, I use SUBTOTAL to sum up 12 columns into a single value. I use MATCH to find the row (sadly, the row headers must be the same on all sheets, in the same order). Put this formula in G17:

    =SUMPRODUCT(SUBTOTAL(9,OFFSET(INDIRECT(F3:F4&"!"&I3&J3),MATCH(F11,INDIRECT(L3&M3&":"&L4&M4),0)-1,0,1,12)))

    That seems to do what you want, but it might be hard to adapt to your workbook if your conditions change at all.
    Thanks Matt! This works really well. How would the formula be modified if I want to make it dynamic with regard to the starting month from which to sum the forward 12 months (e.g. months 3-14)?

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,730
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: SumIf Across Multiple Worksheets

    The red 0:

    =SUMPRODUCT(SUBTOTAL(9,OFFSET(INDIRECT(F3:F4&"!"&I3&J3),MATCH(F11,INDIRECT(L3&M3&":"&L4&M4),0)-1,0,1,12)))

    says which column to start in. 0 for the first column, 1 for the second column, 2 for the third column, etc. The blue 12 says how many columns to include. So for your question, just change the red 0 to a 2.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #5
    Board Regular
    Join Date
    Nov 2010
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SumIf Across Multiple Worksheets

    Quote Originally Posted by Eric W View Post
    The red 0:

    =SUMPRODUCT(SUBTOTAL(9,OFFSET(INDIRECT(F3:F4&"!"&I3&J3),MATCH(F11,INDIRECT(L3&M3&":"&L4&M4),0)-1,0,1,12)))

    says which column to start in. 0 for the first column, 1 for the second column, 2 for the third column, etc. The blue 12 says how many columns to include. So for your question, just change the red 0 to a 2.
    Eric, Thanks so much for your help. This formula works well in the example I posted, but when I try to incorporate it into my larger model, I can't seem to get it to work. I've poured over the formula and have set it up the same way as in my example. The formula seems to be getting tripped up at MATCH($A132,INDIRECT(AB10&AA132&":"&AB10&AB132),0). That part of the function produces #N/A in my larger model. In the example above, it produces the correct row value.

    Not sure if this give you enough information to diagnose the problem, but thought I would check and see. I appreciate all the help you've provided.

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,730
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: SumIf Across Multiple Worksheets

    Without seeing your larger model, I'm just guessing. But a few things to look at:

    In your INDIRECT, you have AB10&AA132&":"&AB10&AB132. Should any of those have $ signs on them? Are you dragging down your formula and you're pulling in incorrect values? Is the range you're looking at on the current sheet? Do you need to add a sheet identifier?

    For the MATCH, does the $A132 actually exist in the lookup range? Exactly? No additional spaces?

    On the Formulas tab, use the Evaluate Formula tool to step through your formula, and you should see exactly what's not working right.

    Good luck!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    Board Regular
    Join Date
    Nov 2010
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SumIf Across Multiple Worksheets

    Quote Originally Posted by Eric W View Post
    Without seeing your larger model, I'm just guessing. But a few things to look at:

    In your INDIRECT, you have AB10&AA132&":"&AB10&AB132. Should any of those have $ signs on them? Are you dragging down your formula and you're pulling in incorrect values? I ADDED ANCHORS BUT THIS IS NOT THE PROBLEM AS I HAVEN'T DRAGGED THE FORMULA. Is the range you're looking at on the current sheet? YES, IT IS. I ADDED A SHEET IDENTIFIER AND STILL NOT WORKING. Do you need to add a sheet identifier? SUMPRODUCT(SUBTOTAL(9,OFFSET(INDIRECT($Z$16:$Z$22&"!"&$AF10&$AA132),MATCH($A132,INDIRECT($Z$16:$Z$22&"!"&$AB$10&$AA$132&":"&$AB$10&$AB$132),0)-1,0,1,12)))

    For the MATCH, does the $A132 actually exist in the lookup range? Exactly? No additional spaces? DOUBLE CHECKED AND $A132 IS EXACTLY THE SAME AS THE VALUE ON THE OTHER WORKSHEETS.

    On the Formulas tab, use the Evaluate Formula tool to step through your formula, and you should see exactly what's not working right. I BELIEVE IT IS THE MATCH COMPONENT OF THE FORMULA.

    Good luck!
    I've added answers to your questions in CAPS above. I've also sent you a PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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