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
<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>
<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>
Thanks for your help in advance, this forum has always been a tremendous help for me...
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 2007A | 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...