3D SUMIF formula

Is it possible to do a 3D SUMIF? By that I mean to SUM a column of values based on the codes in another column through multiply sheets?

By Neale Blackwood

Yes, but the formula is complicated. Assume three sheets named, Jul, Aug and Sep. In a fourth sheet you want to do a SUMIF in all these sheets to calculate a total for the first quarter. The sheets all have the same layout. Column A contains the code you want to match to, and column B contains the value you want to SUM based on the code in Column A. Cell A4 contains the code to which you’d like to match. Here’s the formula, and note it uses the curly brackets {} of an array formula within the formula itself. This is not an array formula; it is entered normally. The formula for a 3D SUMIF is:

=SUM(SUMIF(INDIRECT({“jul”,”aug”,”sep”}&”!A:A”),A4,
INDIRECT({“jul”,”aug”,”sep”}&”!B:B”)))

To include more sheets, just insert more names separated by commas within both sets of the curly brackets {}. The references “!A:A” and “!B:B” refer to full columns. I did this to shorten the formula and make it easier to read. Referring to full columns is inefficient and would slow the calculation time down if used extensively. It is more efficient to use actual ranges such as “!A1:A1000” and “!B1:B1000”.
If your sheet name are numbered, it may simplify the formula slightly. For example, if the sheets are named M1, M2 and M3 the formula would be:

=SUM(SUMIF(INDIRECT(“M”&{1,2,3}&”!A:A”),A5,
INDIRECT(“M”&{1,2,3}&”!B:B”)))

The INDIRECT function allows you to build cell references from text. The curly brackets work as an array and allow you to create multiple references. The SUMIF function works as per normal using the three separate references created by the INDIRECT functions. The SUM function adds up the three SUMIFs. If you didn’t have the SUM function, the result is a SUMIF on the first sheet in the series only.