If you need to know how many records meet a rule, COUNTIF and COUNTIFS are usually more useful than manually filtering and counting the visible rows.
COUNTIF handles one condition. COUNTIFS handles multiple conditions and counts rows where all supplied criteria are met.
COUNTIF syntax
The range is where Excel looks, and the criteria defines what should count.
Example: count a status
| Status |
|---|
| Paid |
| Pending |
| Paid |
| Paid |
If statuses are in A2:A5, use:
The result is 3.
Count a number condition
COUNTIF can count values above, below or equal to a threshold.
This counts cells in B2:B100 that contain a number of at least 1,000.
Use a cell for the criterion
If the threshold is in D2, connect the comparison operator to the cell:
This makes the workbook easier to reuse because changing D2 changes the result without changing the formula.
COUNTIFS for multiple conditions
For example, suppose a sales table has Region in A, Status in B and Amount in C. To count North orders that are still Pending:
Each additional range/criteria pair adds another condition. The row must satisfy all of them to be counted.
Count dates in a range
Date reporting is a common reason to use COUNTIFS. To count records in January 2026 when dates are in A2:A100:
Using the first day of the next month as the exclusive upper boundary is useful when the source data may contain times as well as dates.
Count blanks and non-blanks
COUNTIF can also help identify incomplete records.
To count cells that are not blank:
These checks are useful for finding missing IDs, statuses or other required fields, but confirm how your sheet represents formulas returning empty text before interpreting the count.
Count text containing a word
Wildcard characters can be useful when the cell contains additional text. For example:
This counts cells containing the word urgent anywhere in the text.
COUNTIF is not case-sensitive
COUNTIF does not distinguish between uppercase and lowercase text. If your workflow needs case-sensitive counting, COUNTIF alone is not enough and a different formula approach is required.
Common COUNTIF and COUNTIFS mistakes
- Using COUNTIF for several rules: Switch to COUNTIFS when all conditions must be satisfied together.
- Incorrect criteria syntax: Comparison operators such as > and < are part of the criteria text.
- Number/text mismatch: A number stored as text may not behave as expected.
- Date stored as text: Date criteria work best when the source contains real Excel dates.
- Unequal ranges: In COUNTIFS, criteria ranges should cover corresponding rows.
- Hidden spaces: Text that looks identical may contain extra spaces and therefore fail to match.
COUNTIF vs COUNTIFS
| Function | Best for |
|---|---|
| COUNTIF | One condition |
| COUNTIFS | Two or more conditions that must all be met |
Practical checklist
- Write the counting question in plain language.
- Identify the range that contains the information being tested.
- Use COUNTIF for one rule and COUNTIFS for several rules.
- Test the formula against a small set of rows that you can count manually.
- Check for text/number or date/text mismatches if the result looks wrong.
Related Excel guides
For adding matching values rather than counting them, see How to Use SUMIF and SUMIFS in Excel. For decision logic, see How to Use IF in Excel. You can also browse the Excel Guides collection.
Quick answer
Use COUNTIF(range, criteria) when one condition is enough. Use COUNTIFS(criteria_range1, criteria1, ...) when every supplied condition must be satisfied.