Management, Supervision & Leadership
Dec 17, 2015
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.
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], …)
- 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″)
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(A2:A790,”Alabama”,C2:C790,”Bike*”)
What are some ways you have used the COUNTIFS function to help you analyze data?
