The COUNTIFS function in Excel counts how many rows meet multiple conditions at the same time. It’s one of the most useful functions for analyzing data because it allows you to apply several criteria across different columns.
In This Guide, You’ll Learn
- What the COUNTIFS function does
- The syntax of COUNTIFS
- How to count rows using multiple conditions
- How to use comparison operators like greater than and less than
- How to use text and date criteria
- Common COUNTIFS mistakes and how to fix them
- Real-world examples of COUNTIFS in Excel
What Does the COUNTIFS Function Do?
The COUNTIFS function counts the number of rows that meet multiple conditions.
Unlike COUNTIF, which handles only one condition, COUNTIFS allows you to test several conditions at once.
For example:
- Count sales from the East region made by Sarah
- Count products with inventory below 10 and status marked Active
- Count orders above $500 placed in January
COUNTIFS only counts rows where ALL conditions are true.
COUNTIFS Function Syntax
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)Argument Definitions:
- criteria_range1 — The first range to evaluate
- criteria1 — The condition for the first range
- criteria_range2 — The second range to evaluate
- criteria2 — The condition for the second range
You can continue adding additional range/criteria pairs as needed.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> COUNTIFS Function Data
Basic COUNTIFS Example
Imagine you have this sales table:
You want to count how many sales were made by Sarah in the East region.
Use:
=COUNTIFS(A2:A5,"Sarah",B2:B5,"East")Excel returns 2
Because Sarah appears twice in the East region.
How COUNTIFS Evaluates Multiple Conditions
COUNTIFS works using AND logic.
That means every condition must be true for a row to be counted.
For example:
=COUNTIFS(A2:A10,"East",B2:B10,"Completed")This counts only rows where:
- Region equals East
AND - Status equals Completed
If only one condition matches, the row is not counted.
Using Greater Than or Less Than Conditions
COUNTIFS can also work with numeric comparisons.
For example, to count sales greater than 15000:
=COUNTIFS(A2:A15,">15000")To count sales between 15000 and 20000:
=COUNTIFS(A2:A15,">=15000",A2:A15,"<=20000")This counts values that are:
- Greater than or equal to 15000
AND - Less than or equal to 20000
Using COUNTIFS with Text Conditions
COUNTIFS works extremely well with text data.
Example:
=COUNTIFS(B2:B11,"East",C2:C11,"Approved")This counts rows where:
- Region is East
AND - Status is Approved
Text criteria are not case-sensitive.
For example:
- “east”
- “EAST”
- “East”
All produce the same result.
Using COUNTIFS with Dates
COUNTIFS can also count rows based on dates.
Example:
=COUNTIFS(A2:A16,">=1/1/2026",A2:A16,"<=1/31/2026")This counts dates occurring during January 2026.
You can also combine date conditions with other criteria.
Example:
=COUNTIFS(A2:A16,">=1/1/2026",A2:A16,"<=1/31/2026",B2:B16,"East")This counts January transactions from the East region.
Using Cell References in COUNTIFS
Instead of typing criteria directly into formulas, you can reference cells.
Example:
=COUNTIFS(B2:B11,F1,C2:C11,F2)Where:
- F1 contains East
- F2 contains Approved
This makes spreadsheets easier to update dynamically.
Wildcards in COUNTIFS
COUNTIFS supports wildcards for partial text matching.
| Wildcard | Meaning |
|---|---|
| * | Any number of characters |
| ? | One character |
Example:
=COUNTIFS(A2:A20,"*Smith*")Counts cells containing Smith anywhere in the text.
Example:
=COUNTIFS(A2:A20,"J?n")Matches:
- Jan
- Jon
- Jen
But not:
- John
Common COUNTIFS Errors
Ranges Are Different Sizes
This causes errors:
=COUNTIFS(A2:A10,"East",B2:B15,"Approved")All criteria ranges must contain the same number of rows.
Forgetting Comparison Operators in Quotes
Incorrect:
=COUNTIFS(C2:C20,>500)Correct:
=COUNTIFS(C2:C20,">500")Comparison operators must be inside quotation marks.
Counting Numbers Stored as Text
If numbers are stored as text, COUNTIFS may not evaluate them correctly.
You may need to:
- Convert values to numbers
- Remove apostrophes
- Use the VALUE function cleanup methods
COUNTIFS vs COUNTIF
| Function | Purpose |
|---|---|
| COUNTIF | Counts using one condition |
| COUNTIFS | Counts using multiple conditions |
Example:
COUNTIF:
=COUNTIF(A2:A20,"East")COUNTIFS:
=COUNTIFS(A2:A20,"East",B2:B20,"Approved")COUNTIFS is more powerful when analyzing complex datasets.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> COUNTIFS Function Data
