The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel. You will find a number of examples for different data types - numbers, dates, text, wildcard characters, non-blank cells and more.
Of all Excel functions, COUNTIFS and COUNTIF are probably most often mixed up because they look very much alike and both are purposed for counting cells based on the specified criteria.
The difference is that the COUNTIF function is intended for counting cells based on a single condition in one range, while COUNTIFS allows using several criteria and ranges. In practice, however, you can use Excel COUNTIF with multiple criteria as well. So, the aim of this tutorial is to help you figure out the most efficient formula for each particular task.
As I've already mentioned, the Excel COUNTIFS function is purposed for counting cells across multiple ranges based on one or several conditions. The COUNTIFS function is available in Excel 2013, Excel 2010, and Excel 2007, so you can use the below examples in any Excel version.
The syntax of the COUNTIFS function is as follows:
criteria_range1- defines the first range to which the first condition (
criteria1) shall be applied, required.
criteria1- sets the condition in the form of a number, cell reference, text string, expression or another Excel function, required. The criteria defines which cells shall be counted and can be expressed as 10, "<=32", A6, "sweets".
[criteria_range2, criteria2]…- these are additional ranges and their associated criteria, optional. You can specify up to 127 range/criteria pairs in your formulas.
In fact, you don't have to remember the syntax of the COUNTIF function by heart. As well as with any other formula, Microsoft Excel will display the function's arguments as soon as you start typing; the argument you are entering at the moment is highlighted in bold.
Excel COUNTIFS - things to remember!
1. You can use the COUNTIFS function in Excel to count cells in a single range by a single condition as well as in multiple ranges with multiple conditions.
2. Each additional range must have the same number of rows and columns as the first range (criteria_range1 argument).
3. Both contiguous and non-contiguous ranges are allowed.
4. If the criteria is a reference to an empty cell, the COUNTIFS function treats it as a zero value (0).
5. You can use the wildcard characters in criteria - asterisk (*) and question mark (?). See this example for full details.
For the detailed description and syntax of the COUNTIF function, please see this tutorial - Excel COUNTIF function.
Below you will find a number of formula examples that demonstrate how to use the COUNTIFS and COUNTIF functions in Excel to handle multiple conditions and ranges.
By and large, COUNTIFS formulas for numbers fall into 2 categories - counting numbers between the two values you specify, and based on several conditions.
Example 1. Counting numbers between X and Y
You can count how many numbers are between the numbers you specify in two ways - using a COUNTIFS function or a difference between two COUNTIF functions.
This formula counts how many numbers between 5 and 10 (not including 5 and 10) are contained in cells C2 through C10.
If you want to count cells based on the same condition but including 5 and 10, add "=" to the criteria like this:
COUNTIF formulas to count numbers between X and Y:
=COUNTIF(C2:C10,">5")-COUNTIF(C2:C10,">=10")- counts how many numbers greater than 5 and less than 10 are in the range C2:C10. The formula will produce the same result as you see in the screenshot above.
=COUNTIF(C2:C10, ">=5")-COUNTIF(C2:C10, ">10")- the formula counts how many numbers between 5 and 10 are in the range C2:C10, including 5 and 10.
Summing up, you use a combination of two COUNTIF functions in the following way. In the 1st function, you count how many numbers are greater than your lower bound value (5 in this example). In the 2nd function, you count how many numbers are greater than the upper bound value (10 in our case). The difference between the first and second number is the result you are looking for.
Example 2. Counting numbers with multiple criteria (AND logic)
Suppose you have a list of products indicating how many items are sold, delivered and in stock. You can use the following formulas to count the numbers of products, at least one item of which has been sold and delivered, i.e. if the values in columns B and D are greater than 0:
Here is one more COUNTIFS formula example with multiple ranges:
=COUNTIFS(B2:B11, 0, C2:C11, 0, D2:D11, 0)
The above formula counts the number of products with zeroes in columns B, C and D. This COUNTIFS formula returns 1 because only Product 5 have "0" in all the three columns.
=COUNTIFS(B2:D11,"=0")would produce a different result because it returns the total count of cells containing a zero.
Example 3. How to use cell references in COUNTIFS formulas
Of course, you can use a cell reference instead of a number in Excel COUNTIFS formulas. Just remember to enclose the operator in quotes ("") and add an ampersand (&) before the cell reference to construct a text string. For more information about the use of an ampersand in COUNTIF and COUNTIFS formulas, please see Excel COUNTIF - frequently asked questions.
So, let's make a COUNTIFS formula with a cell reference equivalent to the formula from the previous example:
=COUNTIFS(B2:B11,"="&C2, C2:C11,"="&C2, D2:D11,"="&C2)
Cell C2 used in the criteria contains a zero value, so the formula will produce exactly the same result you see in the screenshot above.
Example 4. COUNTIF formulas for non-contiguous ranges (OR logic)
Examples 2 and 3 above demonstrate how to use COUNTIFS in Excel to count cells based on several conditions with the AND logic. Such formulas can be applied both to contiguous or non-contiguous ranges. For example, we have used this formula
=COUNTIFS(B2:B11,">0", D2:D11,">0") to find out how many products have a value greater than "0" both in column B and column D.
But what if you want a total count of cells with a certain value in several non-adjacent ranges or when at least one of the conditions is met (like OR operator)? For instance, how do you count the total number of zero values in columns B and D? The answer is to use a combination, more precisely, a sum of several COUNTIF functions:
=COUNTIF(B2:B11,"=0") + COUNTIF(C2:C11,"=0")
In a similar manner, you can add up several COUNTIFS functions if you want to count something with the OR logic. For example, the following formula counts the number of rows that have either "Product1" or "Product2" in column A and 0 in column B:
=COUNTIFS(A2:A11,"Product1", B2:B11, 0) + COUNTIFS(A2:A11,"Product2", B2:B11, 0)
The COUNTIFS and COUNTIF formulas you use for dates are very much similar to the above formulas for numbers.
Example 1. Count dates in a specific date range
To count the dates that fall in a certain date range, you can also use either a COUNTIFS formula with two criteria or a combination of two COUNTIF functions.
For example, the following formulas count the number of dates in cells C2 through C10 that fall between 1-Jun-2014 and 7-Jun-2014, inclusive:
=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<=6/7/2014")
=COUNTIF(C2:C9, ">=6/1/2014") - COUNTIF(C2:C9, ">6/7/2014")
Example 2. Count dates with multiple conditions
In the same manner, you can use a COUNTIFS formula to count the number of dates in different columns that meet 2 or more conditions. For instance, the below formula will find out how many products were purchased after the 20th of May and delivered after the 1st of June:
=COUNTIFS(C2:C9, ">5/1/2014", D2:D9, ">6/7/2014")
Example 3. Count dates with multiple conditions based on the current date
You can use Excel's TODAY() function in combination with COUNTIF to count dates based on the current date.
For example, the following COUNTIF formula with two ranges and two criteria will tell you how many products have already been purchased but not delivered yet.
=COUNTIFS(C2:C9, "<"&TODAY(), D2:D9, ">"&TODAY())
This formula allows for many possible variations. For instance, you can tweak it to count how many products were purchased more than a week ago and are not delivered yet:
=COUNTIFS(C2:C9, "<="&TODAY()-7, D2:D9, ">"&TODAY())
In the previous article, I provided an example of Excel COUNTIF formula with two criteria to count the number of cells with 2 different text values in a single range. Now let's see how you can use the COUNTIFS function with multiple criteria and across multiple ranges.
Example 1. COUNTIFS for text values
Suppose, you have a table listing the results of the credits and exams passed by students. What you want to know is how many students have passed all the credits. To do this, you use a usual COUNTIFS formula with several different ranges and the same criteria:
If you want to know how many credits a particular student has passed or not passed, you can utilize either a COUNTIFS or COUNTIF formula, because this time you need a single range only.
For example, the below formulas will tell you how many credits Bella has passed:
Example 2. COUNTIFS with wildcard characters
As I mentioned at the beginning of this tutorial, you can use the following wildcard characters in Excel COUNTIFS formulas:
- Question mark (?) - matches any single character, use it to count cells starting and/or ending with certain characters.
- Asterisk (*) - matches any sequence of characters, you use it to count cells containing a specified word or a character(s) as part of the cell's contents.
Now let's see how you can use a wildcard char in your real COUNTIFS formulas in Excel. Suppose, you a list of projects assigned to your company's employees. You want to find how many projects are already assigned to someone, i.e. any name is stated in column A. And because we are leaning how to use the COUNTIFS function with multiple criteria, let's add a second condition - the End Date in column D should also be set.
Here is the formula that works a treat:
Please note, you cannot use a wildcard character criteria with the second range because you have dates rather that text values in column D. That is why, you use the criteria that finds non-blank cells - "<>"&""