conditional sum across tabs - sumif() vs sumifs()

DEllis81

Board Regular
Joined
Jan 19, 2009
Messages
106
Yesterday, I posted to this forum requesting help with conditional sums across tabs.

The basic formula I have in place...
''=SUMPRODUCT(SUMIFS(INDIRECT(TabGlobal&"!"&$A$3),INDIRECT(TabGlobal&"!"&$A$2),B10))appears to work, when the rangename in A3 is comprised of a single column.

As you can see, in the first screen dump, the conditional tab with sumif() does work, but the equivalent with sumifs() does not. I can change the defined range (bdata) to any column (within my dataset range 0-BY, in this case) - and the second formula is valid. However, change bdata to multiple columns, it does not...

My need for the sumifs - is to develop conditional summations across time intervals (columns), but my first step is to get the sumifs() function to work properly for a multicolumn range)

Excel 2007
A
B
C
D
1
RngeName
Range
2
bLineDesc
=WeanFinish!$A$7:$A$179
3
bdata
=WeanFinish!$O$7:$P$179
4
5
6
7
8
9
TabGlobal
10
FEED - FIXED COSTS
=SUMPRODUCT(SUMIF(INDIRECT(TabGlobal&"!"&$A$2),B10,INDIRECT(TabGlobal&"!"&$A$3)))
11
FEED - FIXED COSTS
=SUMPRODUCT(SUMIFS(INDIRECT(TabGlobal&"!"&$A$3),INDIRECT(TabGlobal&"!"&$A$2),B10))

<TBODY>
[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: 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="bgcolor: #ffff00"]WeanFinish
[/TD]

[TD="align: right"]42,356.25
[/TD]

[TD="bgcolor: #ffff00"]WeanFinish
[/TD]

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

</TBODY>
Sheet1
Excel 2007
A
B
O
P
Q
R
S
T
4
Inputs in Red
5
6
01/12/11
01/19/11
01/26/11
02/02/11
02/09/11
7
1
2
3
4
5
6
8
Inventory
46
47
EXPENSES
48
FEED - Farrowing Creep Feed
49
FEED - FIXED COSTS
50
FEED - FGM
51
FEED - SBM
52
FEED - CORN

<TBODY>
[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="bgcolor: #ffff00"]Projected
[/TD]
[TD="bgcolor: #99ccff"]Projected
[/TD]
[TD="bgcolor: #99ccff"]Projected
[/TD]
[TD="bgcolor: #99ccff"]Projected
[/TD]
[TD="bgcolor: #99ccff"]Projected
[/TD]
[TD="bgcolor: #99ccff"]Projected
[/TD]

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

[TD="bgcolor: #ffff00"]01/05/11
[/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: 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"]21,178
[/TD]
[TD="align: right"]21,189
[/TD]
[TD="align: right"]21,197
[/TD]
[TD="align: right"]21,205
[/TD]
[TD="align: right"]21,223
[/TD]
[TD="align: right"]21,242
[/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"]25,148
[/TD]
[TD="align: right"]25,161
[/TD]
[TD="align: right"]28,057
[/TD]
[TD="align: right"]28,068
[/TD]
[TD="align: right"]28,092
[/TD]
[TD="align: right"]28,116
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]76,186
[/TD]
[TD="align: right"]76,227
[/TD]
[TD="align: right"]78,089
[/TD]
[TD="align: right"]78,118
[/TD]
[TD="align: right"]78,185
[/TD]
[TD="align: right"]78,252
[/TD]

</TBODY>
WeanFinish


Thanks for your help in advance, this forum has always been a tremendous help for me...
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I have created a very small file, as an example describing above issue. Is there a way, I can share this file for assistance. Hopefully, this will help clarify my question, and generate a solution...

I am also finding, the sumif() is only returning the results of the first column, when bdata is defined with multiple columns.

Thanks!
 
Upvote 0
I have created a very small file, as an example describing above issue. Is there a way, I can share this file for assistance. Hopefully, this will help clarify my question, and generate a solution...

I am also finding, the sumif() is only returning the results of the first column, when bdata is defined with multiple columns.

Thanks!
Both SUMIF and SUMIFS will only work on 1 dimensional ranges.

I don't mean to sound critical but you're not explaining what you want to do in a clear consise manner which is probably why you're not getting any respones.
 
Upvote 0
Thank you! I will try to do a better job next time...

Yes - after reviewing your response, I have another model with sumifs() function across tabs, but in that case, I am using a single diminsion range name for the "summation". Let me contemplate if I can change things around, to better match how I did things elsewhere... Is there a way to "autocreate" a new range name, when a new line item is added by user?

Are there any workarounds to the 1 dimensional limitation? I am not allowed to install the threed function on networked computers..
 
Upvote 0
Thank you! I will try to do a better job next time...

Yes - after reviewing your response, I have another model with sumifs() function across tabs, but in that case, I am using a single diminsion range name for the "summation". Let me contemplate if I can change things around, to better match how I did things elsewhere... Is there a way to "autocreate" a new range name, when a new line item is added by user?
Sounds like something you would need to do with VBA. I'm not much of a programmer so I couldn't help you with that.

Are there any workarounds to the 1 dimensional limitation? I am not allowed to install the threed function on networked computers..
Not with those specific functions, no.

In general, trying to use conditional 3D references in Excel is not easy.

Here's an example from the other day:

http://www.mrexcel.com/forum/showthread.php?646305-Sum-Across-Worksheets-with-Two-Criteria

As you'll see, the formula is complex and inefficient.
 
Upvote 0
Thanks for the help... I will try the methodology provided, but not right now... I am doing more of these type summations, and could be worth my while to learn this technique or try to find a way of using threed within the morefunc functions...

And I will try to do a better job explaining, this forum has always been great providing help - as I don't want to abuse the generosity of those helping me!!!
daryl
 
Upvote 0

Forum statistics

Threads
1,226,698
Messages
6,192,514
Members
453,728
Latest member
Ishtiak Mahmud

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