Math & aggregation
How to Use COUNTIFS in Excel (Count on Multiple Criteria)
COUNTIFS counts how many rows meet two or more conditions at the same time: orders that are both West and Paid, values between 50 and 100, open tickets raised this year.
It is the multi-condition version of COUNTIF, joining every condition with AND. This guide covers text, number-range, and date criteria with ready-to-use formulas.
COUNTIFS syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Argument | Required | What it does |
|---|---|---|
criteria_range1 | Required | The first range to test against a condition. |
criteria1 | Required | The condition for range 1: "West", ">=50", or ">="&E1. |
criteria_range2 | Optional | A second range to test (up to 127 pairs). |
criteria2 | Optional | The condition for range 2. All conditions must be TRUE (AND logic). |
Note: Every condition must be true for a row to count (AND logic). Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Two text conditions
=COUNTIFS(A:A, "West", B:B, "Paid") Counts only rows where A equals West AND B equals Paid. Each condition narrows the count further.
2. Count between two numbers
=COUNTIFS(B:B, ">=50", B:B, "<=100") Testing the same column twice creates a between filter. The operators sit inside quotes with the numbers.
3. Date plus text condition
=COUNTIFS(A:A, ">="&DATE(2026,1,1), C:C, "Open") Join the operator to DATE() with & so Excel reads it as a comparison, then add the text condition as a second pair.
How to write COUNTIFS step by step
- 1
Click the result cell and type =COUNTIFS(
- 2
Select the first range to test, then a comma.
- 3
Type the condition in quotes ("West" or ">=50"), then a comma.
- 4
Add the next range and its condition the same way for each extra rule.
- 5
Close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
Result is 0 | A criteria does not match, usually a number stored as text or stray spaces. | Confirm each test range and criteria are the same data type; clean text with TRIM(). |
#VALUE! | The criteria ranges are different sizes or shapes. | Make every range the same number of rows and the same orientation. |
Operator ignored | A comparison like >E1 was written without joining the cell reference. | Concatenate the operator and reference with &, e.g. ">="&E1. |
#NAME? | The function name is misspelled, e.g. COUNTIF used where COUNTIFS was meant. | Use COUNTIFS (plural) when you have more than one condition. |
Frequently asked questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF takes a single condition; COUNTIFS takes multiple range/criteria pairs and counts rows where all of them are true.
Can COUNTIFS use OR logic?
No. COUNTIFS only does AND. For OR, add separate COUNTIFS results together, or use SUMPRODUCT.
How do I count between two numbers?
Test the same column twice: =COUNTIFS(B:B, ">=50", B:B, "<=100") counts values from 50 to 100 inclusive.
Does COUNTIFS work in Google Sheets?
Yes. COUNTIFS works identically in Google Sheets, including number ranges and date comparisons.