Tuesday, July 05, 2005

3 Dimensional SUMIFs

John McGimpsey writes this excellent article about using SUMIF across multiple sheets.

Three dimensional SUMIF()s
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...

The morefunc.xll add-in
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.

Summing SUMIF()s on each sheet
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().

SUMIF()s on the summary sheet
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.


Posted by: Jethro | 12:33 PM |