What is COUNTIF in Google Sheets?
COUNTIF returns how many cells in a range meet a single criterion. It powers KPI tiles like open ticket count, rows flagged Yes, or cells above a quota. The criterion follows the same string, comparison, and wildcard rules as SUMIF.
When to use it
Use COUNTIF for tallies: how many orders are Pending, how many scores passed, or how many emails contain @company.com.
When to skip it
Skip COUNTIF when you need multiple AND conditions across columns. Use COUNTIFS. When you need to sum values instead of counting rows, use SUMIF.
How it works
-
1
Point COUNTIF at one range to search, such as status column F2:F500.
-
2
Set criterion as exact text, comparison, cell reference, or wildcard pattern.
-
3
COUNTIF counts cells in the range where the test is TRUE.
-
4
Blank criterion behavior differs from counting blanks explicitly with "".
-
5
Use COUNTIF on numeric ranges with comparisons like ">50" for threshold counts.
-
6
Combine several COUNTIF calls when you need OR logic across different values.
Examples in Google Sheets
Count open tasks
=COUNTIF(E2:E, "Open") returns how many rows in column E still show Open.
Count passing grades
=COUNTIF(C2:C, ">=70") counts scores at or above the pass line.
Count non-empty IDs
=COUNTIF(A2:A, "<>") counts rows where column A is not blank.
Better Sheets resources
Common mistakes
-
Hidden spaces in source data so "Done" does not match Done.
-
Using COUNTIF on one column when the rule should test two columns. Switch to COUNTIFS.
-
Expecting COUNTIF to sum values. It only counts how many cells match.
-
Case sensitivity surprises on mixed-case labels without UPPER cleanup.
-
Counting whole column F:F forever on a 100k row sheet when a bounded range is enough.
Frequently asked questions
- COUNTIF vs COUNTA?
- COUNTA counts non-empty cells regardless of value. COUNTIF counts only cells matching your rule.
- COUNTIF blank cells?
- Use criterion "" to count blanks or "<>" to count non-blanks in the range.
- COUNTIF with dates?
- Compare to DATE or a date cell. Text that looks like a date may not match.
- COUNTIF vs COUNTIFS?
- COUNTIF is one test on one range. COUNTIFS requires every paired range and criterion to match.
- Wildcards in COUNTIF?
- Yes. * matches any run of characters and ? matches one character inside quotes.
- COUNTIF on checkboxes?
- Use TRUE or FALSE as the criterion for checkbox columns.
- Why COUNTIF too high?
- Partial wildcard matches or numbers stored as text with stray characters can inflate counts.
- COUNTIF across sheets?
- Use a tab prefix on the range, such as COUNTIF(Orders!D2:D, "Shipped").
Related Tutorials
Watch how COUNTIF works
How to Iterate Counting Blank Cells in Google Sheets
3 More Ways to Count in Google Sheets
Spreadsheet Automation 101 Lesson 2: If ( ){ } and Checkboxes
Related blog posts
Guides that explain COUNTIF in more depth.
The Most Popular Google Sheet Tutorial on the IF() Formula
The IF() formula is a logical function that makes it easy for users to compare a certain value and an expected value. It takes a look at a value to see if basic conditions are met.
Read post →5 Magical Ways To Use the IF() Formula in Google Sheets
5 ways to use the IF function in Google Sheets ISBLANK() Checkbox AND() OR() IF(IF())
Read post →Learn Google Sheet Formulas The Hard Way
It is so stupidly simple as well that you might not do it and you might not think you'll get enough out of it. But I can tell you unequivocally: This is very much how I learned a lot of Google Sheet formulas.
Read post →Related terms
COUNTIFS
COUNTIFS counts how many rows satisfy every criterion at once. You pass criteria_range1, criterion1, criteria_range2, criterion2, and so on. Unlike COUNTIF, there is no separate count range: each pair tests the same row index across the sheet.
Read guide →SUMIF
SUMIF totals values in a sum_range when a matching row in criteria_range meets your criterion. It is the classic single-condition sum: add column B where column A equals West. Wildcards and comparison operators work in the criterion string.
Read guide →FILTER
FILTER returns only the rows from a range that meet one or more conditions. Conditions are boolean arrays the same height as the data. FILTER spills results automatically, so it replaced many INDEX/SMALL helper-column patterns for live subsets.
Read guide →Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →Done reading about COUNTIF?
Membership unlocks 600+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.