SumIf Across Multiple Worksheets

[chet645]

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
Sheet Reference
Sheet1
Sheet2
Cost Category
a
b
c
d
e
f
g
RESULT

<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>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Sum Range[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Criteria 1[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Criteria 2[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Column[/TD]
[TD="align: center"]Row[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Column[/TD]
[TD="align: center"]Row[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Column[/TD]
[TD="align: center"]Row[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]f[/TD]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"]f[/TD]
[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]#VALUE![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G17[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

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.

 

[chet645]

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)?

 

[Eric W]

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.

 

[chet645]

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.

 

[Eric W]

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!

 

[chet645]

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.