This site will look better if you upgrade to a browser that supports web standards.

## 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.

This page last updated Wednesday, 10 March 2004

© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.