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))

 

Back to top...

OfficeArtilces.com debuted on May 26, 2005.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2014 by MrExcel Consulting | All rights reserved