Excel COUNTIFS function counts cells based on one or more conditions across a single or multiple data ranges. COUNTIFS function is used for data analysis, for example counting sales by date, age and size.
In this tutorial:
How to use COUNTIFS Function in Excel
COUNTIFS formula syntax is:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
- criteria_range1 (required): first data range to test for condition.
- criteria1 (required): condition to test on first data range.
- criteria_range2, criteria2 (optional): additional ranges and related conditions for each.
First data range and criteria are required arguments for the formula to work. Additional data ranges and criteria are optional. Ability to add multiple criteria and ranges easily to analyze complex scenarios make COUNTIFS function one of the best data analysis tools in Excel.
Criteria argument can take:
- text: such as, “Gold”, “LHR-77”, “786”;
- number: like, 770, 100000;
- cell reference: for example, H7, B21
- expression: that is, “< 7/14/2017”, “>77”, “=”&H7, “<“&MAX(A1:A7), “LHR*”, “???”
COUNTIFS formula is very flexible when it comes to criteria. It can take formulas that dictate which cells to count. COUNIFS also supports wildcards in criteria argument including asterisk (*), question mark (?) or tilde (~).
When to use COUNTIFS Function in Excel
COUNTIFS function in Excel is a tool that helps us do conditional counts based on multiple criteria across multiple ranges. It is perfect for scenarios requiring multiple conditions to be applied and each condition is structured differently.
Some of the situations where one can use COUNTIFS are following:
- Project management: count the number of tasks exceeding specific stage of completion percentage by particular site manager.
- Aging analysis: count the number of debtors or receivables that are unpaid for more than a certain period of time. For example, count debtors that have not paid for more than 120 days.
- Sales analysis: count the number of sales agent exceeding a specific sales volume of specific product in certain range of dates. For example, find how many sales agents sold more than 500 units of “dash charger” from March 20th till April 14th.
- Stock analysis: count the items that were ordered certain number of times in the last month. Or count the inventory that is unsold for over a year exceeding a certain threshold.
- Student performance: count number of students who scored “A” grade in certain subject. Or count the number of pupils who improved their marks in this term over the previous term in specific subject.
Excel COUNTIF vs COUNTIFS
Excel COUNTIF function counts number of cells meeting one condition only within a single data range. COUNTIF function can take only one range-criteria pair. It is simple to write and best for conditional count based on single criterion.
Meanwhile, COUNTIFS function in Excel can count cells that meet one or multiple condition within a single data range or more than one data ranges. COUNTIFS function can take up to 127 range-criteria pairs.
COUNTIFS Formula with single criterion or condition
In the following example, we want to know how many sales transactions occurred in “East” region:
As we have a single criteria to match, the formula is:
=COUNTIFS(C7:C22,"East")
- C7:C22 is the data range we want to check against criteria.
- “East” is the criteria we are using to find how many times exact matches are found in the data range. The formula used a text value as criteria argument.
Text value as criteria must be in double quotes or inverted commas for COUNTIFS function to count correctly.
The Formula counts how many cells contain “East” in the range C7:C22:
COUNTIFS Formula with Cell References
COUNTIFS function can take cell references as criteria. Instead of hard coding “East” in the formula we can refer to a cell mentioning region.
With region mentioned in cell E3, formula is:
=COUNTIFS(C7:C22,E3)
The formula counts the number of cells matching the criteria mentioned in cell E3. This time we are using cell reference instead of text value as criteria.
Cell reference as criteria needs not to be in double quotes.
Great thing about this approach is we can easily change the region from East to West, North etc. to know respective count:
COUNTIFS Function in Excel with Multiple Criteria
If scenario demands more than one condition to meet, COUNTIFS formula allows you to add multiple conditions easily.
For instance, we want to count number of sales transactions in “East” region by agent “Bey Twice”:
Simply include the additional criterion with the relevant range after the first one in the COUNTIFS formula:
=COUNTIFS(C7:C22,E3,D7:D22,E4)
- C7:C22: first range that contains the names of regions
- E3: cell address with the first condition to meet i.e. only count cells containing “East” in range C7:C22
- D7:D22: second range containing the names of agents
- E4: cell address with the second condition to meet i.e. only count cells containing “Bey Twice” in data range D7:D22
COUNTIFS will join both conditions using AND logic, and will only count cells that meet both conditions:
To add more conditions, simply repeat the steps similar to second condition and add relevant range with criteria in the formula.
Suppose we want to count “McDiabetes” sales by “Bey Twice” in “East”. The formula is:
=COUNTIFS(C8:C23,E3,D8:D23,E4,E8:E23,E5)
COUNTIFS Formula with Logical Operators – Greater than, Less than, Equal to
COUNTIFS function in Excel supports logical operators in criteria argument. This includes greater than “>”, less than “<“, equal to “=”, not equal to “<>” or variations of these logical operators.
While including logical operators, entire criteria needs to be in double quotes.
To count sales transactions exceeding $7000 the formula is:
=COUNTIFS(F6:F21,">7000")
Following table shows different variations of COUNTIFS formula with logical operator based on above example:
| Criteria | Formula | Explanation |
|---|---|---|
| Count if greater than | =COUNTIFS(F6:F21,”>7000″) | Count cells with sales value greater than 7000 |
| Count if less than | =COUNTIFS(F6:F21,”<7000″) | Count cells with sales value less than 7000 |
| Count if equal to | =COUNTIFS(F6:F21,”=7000″) | Count cells with sales value equal to 7000 |
| Count if not equal to | =COUNTIFS(F6:F21,”<>7000″) | Count cells with sales value not equal to 7000 |
| Count if greater than or equal to | =COUNTIFS(F6:F21,”>=7000″) | Count cells with sales value greater than and equal to 7000 |
| Count if less than or equal to | =COUNTIFS(F6:F21,”<=7000″) | Count cells with sales value less than and equal to7000 |
COUNTIFS with logical operator and cell reference
To refer a cell with logical operator as criteria, only the logical operator part is enclosed in double quotes and concatenated with cell address using ampersand (&) sign.
With sales value mentioned in cell E3, to count how many sales exceeded the value:
=COUNTIFS(F6:F21,">"&E3)
COUNTIFS Function with Dates
Excel COUNTIFS can make conditional counts based on dates data. For example, to count sales transactions before or after a certain date, count loans that are payable after a certain date etc.
For example, to count sales after 30th March 2024 the formula is:
=COUNTIFS(B6:B21,">3/31/2024")
We can simplify this formula further by having date in a cell and referring to it in formula. With date value in cell E3:
=COUNTIFS(B6:B21,">"&E3)
Count between dates
COUNTIFS can count cells based on certain period of time with specific range of dates. It is just another example of COUNTIFS with multiple criteria with first criteria as a start date and the second one is an end date.
To count sales transactions between February 15, 2024 and April 15, 2024 the formula is:
=COUNTIFS(B6:B21,">=2/15/2024",B6:B21,"<=4/15/2024")
If we have both dates in separated cells, we can refer to each as shown here:
=COUNTIFS(B6:B21,">="&D3,B6:B21,"<="&E3)
COUNTIFS with Multiple Criteria in different column (multiple ranges)
Usually multiple criteria are based on different columns where we have to select respective data range for each condition.
If we want to count how many “StarSucks” “Tom Softy” has sold then formula is:
=COUNTIFS(D7:D22,D4,E7:E22,E4)
COUNTIFS with Multiple Criteria in the same column (same range)
Usually scenarios with multiple criteria are based on different column. If we have more than one condition to apply on the same column, you simply have to select the same column again.
To count sales that are greater than 5000 but less than 7000, formula can be:
=COUNTIFS(F7:F22,">="&E3,F7:F22,"<="&E4)
Excel COUNTIFS Function with OR logic
By default, COUNTIFS function in Excel appends each logic with AND logic. But situations can require counting cells based on OR logic instead.
To count how many sales transactions are made by either Bey Twice or Tom Softy, the formula is:
=COUNTIFS(D7:D22,"Bey Twice")+COUNTIFS(D7:D22,"Tom Softy")
A better approach to achieve the same result is by using one COUNTIFS function with criteria mentioned as an array constant.
With COUNTIFS function we can have AND with OR logic in the same Excel formula. For instance, if we like to count sales greater than 5000 made either by Bey Twice or Tom Softy then formula can be:
=SUM(COUNTIFS(D7:D22,{"Bey Twice", "Tom Softy"},F7:F22,">5000"))
Similarly COUNTIFS supports multiple OR criteria using array constants as criteria argument for each range.
For example, to count sales made by “Bey Twice” or “Tom Softy”
selling either “McDiabetes” or “FedUpEx”, the formula is:
=SUM(COUNTIFS(D7:D22,{"Bey Twice","Tom Softy"},E7:E22,{"McDiabetes";"FedupEx"}))
Note the difference between two array constants. First one has a comma, the second has a colon. This allows Excel to arrange both criteria like a table with first array horizontally like a header of the table and second one as table rows vertically:
| Bey Twice | Tom Softy | |
|---|---|---|
| McDiabetes | 1 | 0 |
| FedUpEx | 1 | 3 |
Sum of the above results is 5 as shown in the illustration above.
Excel COUNTIFS with Wildcards
Till now we have learnt to count based on exact match. In exact-match, we use a text value as criteria to count cells that match exactly as the text value provided. And when a cell reference is used, contents of cell are used to count cells that make exact match.
To make partial-match i.e. count cells based on value that is part of the cell contents, we can use wild-card characters in criteria argument of COUNTIFS function in Excel.
In Excel COUNTIFS formula we can use following three types of wildcard characters:
- asterisk (*),
- question mark (?) and
- tilde (~)
For example, if we want to count sales of products that start with letter “S” then formula is:
=COUNTIFS(E7:E22,"S*")
Similarly to count how many sales are “Jam” i.e. products that end with word “Jam” the formula is:
=COUNTIFS(E7:E22,"*Jam")
Another example where the name of a person is written in multiple styles, it will be hard to count his sales using exact-match methods. COUNTIFS partial-match technique using asterisk (*) works best in this situation:
=COUNTIFS(D7:D22,"*Softy*")
