This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
XL does not do 3-dimensional ranges very well. Often there's a need for a conditional sum over a number of worksheets (perhaps for a summary sheet that contains conditional sums over 12 monthly sub-sheets). It would be nice to write on sheet Summary:
=SUMIF(Jan:Dec!$D$4:$D$100, A1, Jan:Dec!E$4:E$100)
Unfortunately, SUMIF() isn't up to the job. There are three alternatives that I know of...
With Laurent Longre's morefunc.xll add-in, you can use the THREED function, which coerces a 3-dimensional range into a single array:
=SUMPRODUCT(--(THREED(Jan:Dec!$D$4:$D$100)=A1), THREED(Jan:Dec!E$4:E$100))
Unfortunately, the add-in is Windows-only, so should not be used in cross-platform development, or in environments where the add-in may not be allowed or available.
This method takes advantage of the fact that SUM() can sum a single cell across sheets. On each sub-sheet, in an out-of-the-way location (say, cell Z1), enter
=SUMIF($D$4:$D$100, Summary!A10, $E$4:$E$100)
Then back on sheet Summary, enter:
=SUM(Jan:Dec!$Z$1)
This method has the advantage of being simple and easily followed six months down the road when you need to make some changes. Note that the SUM() works by position - if, say, Sheet Apr is moved to the left of sheet Jan or to the right of sheet Dec, it will no longer be included in the SUM().
Of course, you can bring all the individual SUMIF()s onto the summary sheet. For instance, in cell Z1, put
=SUMIF(Jan!$D$4:$D$100, A1, Jan!$E$4:$E$100)
Similarly in Z2, substitute "Feb" for "Jan", until in Z12 you have "Dec!$D$4...". Name Z1:Z12 as "My_SumIfs", then enter
=SUM(My_SumIfs)
Alternatively, you can put the names of the sheets in Z1:Z12 and use SUMPRODUCT to sum an array of SUMIF()s:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$Z$1:$Z$12&"'!D4:D100"), A1, INDIRECT("'"&$Z$1:$Z$12&"'!E1:E100")))
The big disadvantage of this technique is that if you rename or add a worksheet (admittedly unlikely with months, but very likely if you're summing one sheet for each salesperson), you also have to update the list in Z1:Z12.
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
