SumIf Across Multiple Worksheets

chet645

Board Regular
Joined
Nov 10, 2010
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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!


FGHIJKLMNOPQRSTUVWX
1Sum RangeCriteria 1Criteria 2
2Sheet ReferenceColumnRowColumnRowColumnRow
3Sheet1I9f9I8
4Sheet2X15f15X8
5
6
7Month
8Cost Category0123456789101112131415
9a184119201531911035315163
10b319177131013122010431372012
11c17164391611181312141306106
12d4216191621150420132131819
13e612415182011317681415150
14f1722541871010220114042
15g1741218100719141511151519914
16
17RESULT#VALUE!

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top