Math & Aggregation FormulaGoogle Sheets V8 Engine Verified
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.
Formula Syntax:
=COUNTIF(range, criterion)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())How it works: Counts dates in Column C earlier than today's date.
Example 2: Audit Non-Empty Cells Containing Text
=COUNTIF(A2:A, "?*")How it works: Counts cells with text while ignoring blank cells and numeric zero.
Example 3: Detect Duplicate Values in Column
=IF(COUNTIF(A:A, A2) > 1, "Duplicate", "Unique")How it works: Identifies whether current cell value appears multiple times in Column A.
Common Errors & Solutions
❌ Quotation mistakes with comparison operators
Resolution: When referencing cell dates, concatenate the operator: `"<=" & A1` instead of `"<A1"`.
Performance Pro Tips
- Use COUNTIFS for multi-column conditions (e.g. status is "Pending" AND age is >30 days).
Frequently Asked Questions
Does COUNTIF recalculate automatically when data changes?
Yes. Google Sheets recalculates all dependent formulas immediately whenever referenced input cells are modified.
Is this formula compatible with Microsoft Excel?
Modern functions like XLOOKUP, FILTER, UNIQUE, SORT, and LET are natively supported in Excel 365. However, QUERY is unique to Google Sheets and uses the Google Visualization API syntax.