Write a SUMIF Multiple very Easily with the New SUMIFS

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_range,criteria_range1,criteria1,criteria_range2,criteria2…) or

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:

Important notice:

  • 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