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)
💡 Best Practice: Always try complex formulas on a copy of your file first before applying them to active production data.

Technical Overview & Engine Mechanics

COUNTIF counts the number of cells within a range that meet a specified condition. It supports numeric comparisons, text matches, wildcards, and blank cell evaluations. In business sheets, COUNTIF is indispensable for tracking survey responses, monitoring open support tickets, auditing inventory status, and calculating attendance percentages. When working with large datasets, combining COUNTIF with wildcards (such as asterisk for substring matches or question mark for single character placeholders) allows powerful fuzzy matching without regular expressions. However, remember that COUNTIF evaluates conditions as text strings, which can occasionally cause subtle type coercion bugs with numerical IDs.

Formula Comparison: COUNTIF vs. Alternatives

COUNTIF handles single conditions cleanly. When multiple conditions are required (e.g. status = 'Open' AND priority = 'Urgent'), use COUNTIFS. COUNTIF evaluates faster than ARRAYFORMULA(SUM(IF(...))).

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
rangeRangeRequiredThe range to audit.
criterionExpressionRequiredThe 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.

Example 4: Count Overdue Invoices

=COUNTIF(DueDateRange, "<" & TODAY())
How it works: Counts all invoice rows where the due date is strictly in the past.

Example 5: Count Partial Text Matches with Wildcards

=COUNTIF(SupportTickets!C:C, "*login error*")
How it works: Counts all support tickets containing the phrase 'login error' anywhere in the description.

Common Errors & Solutions

❌ Quotation mistakes with comparison operators
Resolution: When referencing cell dates, concatenate the operator: `"<=" & A1` instead of `"<A1"`.
❌ Quoting errors when combining operators and cell references
Resolution: Correct syntax is "<" & A1. Quoting the cell reference inside the string ("<A1") treats A1 as literal text.
❌ Counting numbers formatted as text
Resolution: COUNTIF evaluates numbers and numeric text differently. Ensure uniform cell formatting in source data.

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")).

Related Google Sheets Formulas