# 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