I need to sum up data on multiple sheets using two criteria, which I've managed to do in two different ways (below). However I'm trying to remove the SUMIFS() function from either of these methods to enable this workbook to function in Excel version 2003 (sadly still used by some of our guys on site).
I'm also posting this so that there's a simple reference for such a solution (below) if you are using Excel 2007 or later - as I see similar posts on many forums about this, SUMPRODUCT on Multiple Sheets with Multiple Criteria, but they are rarely generic and can be difficult to follow for others.
To explain the formulae below, on each sheet there's three named columns:
1 - Critera1Range
2 - Critera2Range
3 - SumRange
Then there's a list of the sheet tab names (again a named range): ListTabs
Highly recommend anyone to use Named Ranges for these references rather than A4:A20 and B4:B20 type references; especially as if the range changes, say you insert extra rows, the formulae below will not update the references to that range within the INDIRECT function. However if your named ranged updates these formulae will still work.
Some people would call this a 3D array, with columns, rows and sheets being the 3 Dimensions.
First I have an Array formula which works fine (for the uninitiated, Array formulae are generated be pressing CTL+SHFT+ENTER when editing the formula)
{=SUM(SUMIFS(INDIRECT(ListTabs&"!SumRange"),INDIRECT(LstTabs&"!Critera1Range"),Criterion1,INDIRECT(ListTabs&"!Critera2Range"),Criterion2))}
Second I've a SUMPRODUCT (non-array) formula which also works fine:
=SUMPRODUCT(SUMIFS(INDIRECT(ListTabs&"!SumRange"),INDIRECT(ListTabs&"!Critera1Range"),Criterion1,INDIRECT(ListTabs&"!Critera2Range"),Criterion2))
So, can anyone help me switch out the SUMIFS in these functions for another SUMIF/SUMPRODUCT/IF statement (Array formula or not)
A minor addition to the INDIRECT references above:
I've ensured there's no spaces within the sheet tab names within ListTabs. If you have spaces or other odd characters like & in your sheet tab names you need to enclose the reference with the ' and would have an INDIRECT formula like this:
INDIRECT("'"&ListTabs&"!SumRange'")
Bookmarks