#### Mac XL

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

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.