Excel 2003 users used one of several workarounds: Helper columns, Array Formulas, SUMPRODUCT, etc.
Hopefully, Excel 2007 brings SUMIFS, so you can forget those complicated methods outlined above.
You can create a SUMIF two, three or N criteria very easily.
Here’s how the function works…
The syntax is:
SUMIFS(sum the items in this range always that, range 1 matches, criteria 1 and, range 2 matches, criteria 2…)
You will now fully master SUMIFS function by exploring a practical example.
We will use the table below.
Imagine you want to know the Sales per Product which Contract date is oldest than or equal to 3/31/2009:
Let’s build the formula…
- sum_range It is the range to sum in case all criteria N are matched. For example: $B$2:$B$8 or “Sales”
- range N It is each column array with the values to be compared against criteria N. For example: $D$2:$D$8 or “Product” against “Ipod”, $C$2:$C$8 or “Salesman” against “John Ballard”, etc
- criteria N It is the value you want to search in each range N specified above. For example: “Ipod” and “John Ballard”
The formula would be: =SUMIFS(Haystack!$B$2:$B$8,Haystack!$D$2:$D$8,A2,Haystack!$A$2:$A$8,B2)
The report would be this one:
- Always cross check with a total SUM (see 31,303.48 in C4 above). You can use a Filter to sum with multiple criteria
- Take care of items not properly taken into account due to spaces or wrong spelling
- Click here if you want to know the complex ways to do a SUMIF Multiple