Google Sheets COUNTIF & COUNTIFS: Conditional Counting & Auditing
COUNTIF counts the number of cells within a range that meet a specific condition, text string, numerical threshold, or wildcard pattern.
=COUNTIF(range, criterion)Technical Overview & Engine Mechanics
Formula Comparison: COUNTIF vs. Alternatives
Formula Parameters & Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| range | Range | Required | The range to audit. |
| criterion | Expression | Required | The pattern or threshold to count (e.g. "Pending", "<>0", "?*"). |
Real-World Spreadsheet Examples
Explore verified operational formulas used in enterprise reporting and database workflows:
Example 1: Count Overdue Invoices
=COUNTIF(C2:C100, "<" & TODAY())Example 2: Audit Non-Empty Cells Containing Text
=COUNTIF(A2:A, "?*")Example 3: Detect Duplicate Values in Column
=IF(COUNTIF(A:A, A2) > 1, "Duplicate", "Unique")Example 4: Count Overdue Invoices
=COUNTIF(DueDateRange, "<" & TODAY())Example 5: Count Partial Text Matches with Wildcards
=COUNTIF(SupportTickets!C:C, "*login error*")Common Errors & Solutions
Performance Pro Tips
- Use COUNTIFS for multi-column conditions (e.g. status is "Pending" AND age is >30 days).
- To count non-empty cells with COUNTIF, use the criterion "<>" which counts any cell containing text or numbers while ignoring truly blank cells.
- Wrap COUNTIF in ARRAYFORMULA or use MAP with LAMBDA if you need to calculate running tally counts across multiple rows dynamically.
Frequently Asked Questions: COUNTIF
Can COUNTIF count blank cells?
Yes: =COUNTIF(A2:A100, "") counts empty cells; =COUNTIF(A2:A100, "<>") counts non-empty cells.
Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase text as identical.
What is the maximum range COUNTIF can scan?
COUNTIF can scan entire columns (e.g. A:A) across all rows in Google Sheets.
How do I count cells greater than an average?
=COUNTIF(A2:A100, ">" & AVERAGE(A2:A100)).
When should I upgrade to COUNTIFS?
Upgrade to COUNTIFS as soon as you have two or more criteria to evaluate simultaneously.
Does COUNTIF work with case-sensitive text matching?
No. Standard COUNTIF in Google Sheets is strictly case-insensitive. If you need case-sensitive counting (e.g. differentiating "PROD" from "prod"), use SUMPRODUCT(EXACT(range, "PROD")).