SUMIF Using Multiple Criteria in Microsoft Excel

Here we describe only the simplest of methods. For an excellent overview of all those fancy SUMIF and SUMPRODUCTS formulas, please check out Bob Phillips' site. Thanks, Bob!

Sum of units sold, where vehicle type is Cars uses just a single criterion:

=SUMIF(A2:A10,”Cars”,C2:C10)

And if we want a sum of units sold for all vehicle types except Cars, we could use:

=SUMIF(A2:A10,”<>Cars”,C2:C10)

Sum of units sold where vehicle type is Cars or Trucks:

=SUMIF(A2:A10,”Cars”,C2:C10)+SUMIF(A2:A10,”Trucks”,C2:C10)

Sum of units sold where vehicle type is Cars and Color is Black. The SUMIF function doesn’t work for multiple criteria in different fields. However, you can use an array formula (sometimes referred to as a CSE formula), which requires you to press Ctrl+Shift+Enter after typing it, instead of just Enter.

=SUM((A2:A10=”Cars”)*(B2:B10=”Black”)*C2:C10)

Sum of units sold where vehicle type is Cars and color is not Black. Again, this is an array formula, so press Ctrl+Shift+Enter after typing it.

=SUM((A2:A10=”Cars”)*(B2:B10<>”Black”)*C2:C10)

Sum of units sold where vehicle type is Cars and Units Sold is greater than or equal to 1000.

Again, this is an array formula, so press Ctrl+Shift+Enter after typing it.

=SUM((A2:A10=”Cars”)*(C2:C10>=1000)*C2:C10)

Sum of “units sold” between 500 and 800 would be another array formula, so press Ctrl+Shift+Enter after typing it.

=SUM((C2:C10>=500)*(C2:C10<=400)*(C2:C10))