Managing Data Analysis with Multiple Conditions

SUM & IF ARRAY as an ALTERNATIVE TO SUMIFS

In Excel, when you need to retrieve data off a table based on a criterion, you can easily use a VLOOKUP or HLOOKUP formula. If you ever want to sum the entire data which meets that single criterio, you use SUMIF in place of VLOOKUP.

Now say you have a great big table of downloaded report (called "SourceData" tabsheet) detailing both sales and expenses information by Cost Centre and Department for the whole year of 2011. On another worksheet that you call "Summary", $A10 is the first criteria (say Cost Centre of your organisation) that is listed down from cell A10 to A30. B$3 is the second criteria (say Department of your organisation) that you placed horizontally from cell B3 to B8.

And so it goes that you need to sum up the data based on multiple criteria. Yes, you can use SUMIFS that unfolds like =SUMIFS(SourceData!$K$2:$K$1000,SourceData!$E$2:$E$1000,$A10,SourceData!$D$2:$D$1000,B$3). But what if you're still using the older versions of Excel (prior to 2007) such as Office 2003, 2002 (XP), or even 2000? You don't have the luxury of SUMIFS function yet. The solution is to utilise the "array flavored" combination of SUM and IF.

Using the same logic of working as SUMIFS, this array formula will generate the same results. It goes like this {=SUM(IF((SourceData!$E$2:$E$1000=$A10)*(SourceData!$D$2:$D$1000=B$3),SourceData!$K$2:$K$1000))}. The uniqueness (or perhaps eccentricity if you want to call it that way) of this formula is as follows:
1. The two criteria are combined by using the multiplication operator (which is "*") which functions like the "AND" condition where you tell Excel that both arguments MUST be met to return the result.

2. It is the IF function that actually enables this formula to have the 'array' flavor because what it basically does is to compare individually IF each cell on range E2:E1000 is the same as cell A10, AND AT THE SAME TIME comparing each cell on range D2:D1000 is the same as cell B3. If both IFs match, please sum all the data up. That's what you want Excel to do.

3. Beware, though, that you have to press CSE combo button (Control+Shift+Enter) for this array formula to work so that the curly bracket {} shows automatically which marks it as an array. Hence, please do NOT type the curly bracket yourself in the Formula Bar.

4. The shortcoming of using an array formula is that you can't copy paste downwards easily like you're used to doing very quickly. Excel will display an error message saying "You cannot change part of an array" - seemingly due to the fact that Excel doesn't even vary the semiabsolute cell addresses that we intentionally use in the formula so they could be carried forward when doing copy & paste. This means you'll have to paste (Ctrl+V) it one at a time throughout the working range. Luckily, you can still copy by row and paste it downwards rather than one for each cell!

In any case, this array combination formula nicely does the job of SUMIFS equally well although you have to put up with its few weirdos. Not bad, really not bad for an alternative as well as a good chance learning about array. (EJ - 19/5/11)