Excel® Tips and Tricks

Get the best in Excel® training anytime, anywhere.

Get Excel® Training

COUNTIFS – Excel’s COUNTIF for Multiple Criteria

As Excel users, we’re never satisfied! We see a powerful function like COUNTIF that gives us the ability to find and tally data based on a huge assortment of specified criteria and we still want more. Namely, the ability to count our data based on multiple criteria specifications. COUNTIF a value is greater than 10 isn’t good enough. We want to find information that is greater than 10, less than 50 and contains the word “blue” in the description column. We’re greedy and we know it.

In Excel 2007 Microsoft gave us the COUNTIFS function and long chains of COUNTIF in formulas became a thing of the past.

Count Cells That Match a Multiple Criteria

In our example spreadsheet, we have a list of products and their prices. We know how many were sold and in what state the order originated. To better understand our business, we want to learn how many orders of Bike Helmets in Georgia sold 4 or more units.

COUNTIFS  - Excel's COUNTIF for Multiple Criteria - image 1

To follow using our example, download Countif excel multiple criteria.xlsx This article applies to Excel 2007 and later versions. Images were taken using Excel 2013 on Windows 7.

The syntax for the COUNTIFS function is:

COUNTIFS(range1, criteria1, [range2], [criteria2], …)>/span>

  • The range argument(s) define the group of cells that you want counted.
  • The criteria argument(s) define the circumstances under which a cell will be added to your count. The criteria can be a number, a cell reference, an expression or a text string.

There are a few additional factors to note when using COUNTIFS:

  • If you use multiple ranges, they do not have to be adjacent but they DO have to have the same number of rows and columns.
  • Enclose non-numeric criteria in double quotes. Example: “Bike Helmet”. Numeric criteria does need quotes.
  • Wildcard characters ? and * can be used in criteria. Note that wildcards work with text but not numeric values. (Hint: If you actually need to find a “?” or “*” character, you will need to put the tilde character in front of it: ~?  or ~*)
  • You can also use ampersand “&” in your arguments to concatenate information from another cell within your criteria. Example: =COUNTIFS(A2:A790,”>”&E6, etc.) The value of E6 is 3, so “>”&E6 would be the same as “>3”.
  • Criteria are cumulative. Results will have met all specified criteria in an AND relationship instead of an OR relationship.

If we want to count how many orders of Bike Helmets in Georgia sold 4 or more units we would break it down as follows:

Range 1 = A2:A790   Criteria 1 = “Georgia”

Range 2 = C2:C790   Criteria 2 = “Bike Helmet”

Range 3 = E2:E790   Criteria 3 = “>=4”

The resulting formula would be:

=COUNTIFS(A2:A790,”Georgia”,C2:C790,”Bike Helmet”,E2:E790,”>=4″)
COUNTIFS  - Excel's COUNTIF for Multiple Criteria - image 2
Let’s do another example using wildcards. We want to learn how many orders from Alabama included items with names that started with “Bike”. This will quickly tally both Bike Racks and Bike Helmets:

Range 1 = A2:A790   Criteria 1 = “Alabama”

Range 2 = C2:C790   Criteria 2 = “Bike*”

The resulting formula would be:

COUNTIFS  - Excel's COUNTIF for Multiple Criteria - image 3
What are some ways you have used the COUNTIFS function to help you analyze data?

Written by

Tepring Crocker is a freelance copywriter and marketing consultant. Her projects include everything from course development and webinars for business training clients such as Fred Pryor Seminars to email, website, and content marketing strategy for small businesses in the Kansas City area. Tepring has been active in internet marketing since 1998 where she started as a product and content manager at Go.com, a search portal owned by Disney.

Leave a Reply

Your email address will not be published. Required fields are marked *