Excel / 5 min read
COUNTIF Formula Examples for Excel and Google Sheets
Count matching values, text labels, thresholds, and duplicates with practical COUNTIF examples you can reuse.
COUNTIF counts how many cells match one condition. It is useful for statuses, duplicates, thresholds, and basic audit checks.
Check this first
- Use the right comparison operator inside quotes.
- Check for hidden spaces if a text match seems wrong.
- Make sure numbers stored as text are not breaking the count logic.
- Use an exact text label before debugging more complex versions.
Working examples
Count rows marked Open
=COUNTIF(B2:B20, "Open")Count values above 500
=COUNTIF(C2:C20, ">500")Why COUNTIF is useful
COUNTIF turns a messy list into a quick summary. It is often the fastest way to answer questions like how many invoices are unpaid, how many tasks are overdue, or how many rows meet a minimum threshold.
Because it is simple, it also works well as a debugging helper beside more complex formulas.
A good audit pattern
One practical pattern is to build a small summary block beside the raw data. Count the number of Open, Closed, and Pending rows before building charts or dashboards. If those counts look wrong, the issue is usually in the source labels.
- Count statuses before building charts.
- Use COUNTIF to spot likely duplicates.
- Check thresholds with separate helper counts.
Common reason results look wrong
The usual culprit is inconsistent source data: extra spaces, different capitalization, or slightly different labels like Open and Open . Standardizing the source column often fixes the formula without changing the logic at all.