How to Use COUNTIF in Google Sheets

To count cells that meet one condition in Google Sheets, use =COUNTIF(B2:B100,"Paid"); this example assumes payment statuses are in B2:B100 and returns the number of cells containing Paid. Enter the formula in an empty cell and press Enter. COUNTIF is useful for quick totals such as counting a status, a particular word, a number above a threshold, or blank cells.

What COUNTIF Does

COUNTIF checks a range of cells against one condition, called a criterion, and returns a single count. It does not add the values in the range or return the matching cells. For instance, you can count how many orders are marked Paid, how many scores exceed a target, or how many entries are missing.

The function has two inputs: the range to inspect and the criterion to test. A range can be a column, a row, or a rectangular block, though a single column or row is usually easiest to understand. The criterion can be a number, text, comparison, wildcard pattern, or a reference to another cell. COUNTIF is not case-sensitive for ordinary text matching, so Paid and PAID are treated as the same text.

Enter a Basic COUNTIF Formula

For a straightforward count in the browser version of Google Sheets, follow these steps:

Choose an empty cell where you want the result to appear.
Type an equals sign followed by COUNTIF, then an opening parenthesis.
Select or type the cells to check, add a comma, and enter the criterion.
Close the parenthesis and press Enter.

With order statuses in B2:B100, this counts cells whose content is Paid:

=COUNTIF(B2:B100,"Paid")

With scores in C2:C30, this counts scores greater than or equal to 80:

=COUNTIF(C2:C30,">=80")

In the second example, the comparison operator and number are one criterion, written inside quotation marks. If you prefer, start typing the formula and click or drag across the range instead of entering its address manually. Google Sheets will insert the selected range into the formula.

Choose the Right Criterion

Criteria work a little differently depending on whether you are matching text, a number, or a comparison. Use these patterns as a guide:

Exact text: With category names in A2:A50, count the cells labeled Books using =COUNTIF(A2:A50,"Books").
Exact number: With quantities in C2:C50, count cells equal to zero using =COUNTIF(C2:C50,0).
Comparison: With ages in D2:D50, count values below 18 using =COUNTIF(D2:D50,"<18"). Operators include >, <, >=, <=, and <> (not equal to).
Criterion from a cell: With status values in B2:B100 and the desired status in E1, use =COUNTIF(B2:B100,E1).

When the comparison threshold is stored in a cell, join the operator to the cell reference with an ampersand. With scores in C2:C30 and the minimum score in E1, use =COUNTIF(C2:C30,">="&E1). This builds a criterion such as >=80 from the operator and the value in E1. A common mistake is to write the operator and reference together inside quotation marks; that makes Sheets treat the reference as literal text instead of reading its value.

Use COUNTIF for Common Tasks

COUNTIF can answer practical questions without sorting or filtering the data. For example, suppose a class records assignment results in column D and uses the word Missing for work not turned in. With results in D2:D40, count the missing assignments using:

=COUNTIF(D2:D40,"Missing")

To count inventory items below a reorder point, suppose stock quantities are in C2:C75 and the threshold is in F1. With those cell locations, the formula is:

=COUNTIF(C2:C75,"<"&F1)

If F1 contains 5, this counts quantities lower than 5, not quantities equal to 5. Use <= if the threshold itself should also be included. The result counts matching cells; it does not identify which items need attention. You can pair the count with a filter or conditional formatting when you also need to locate those rows.

You can also count blank cells. With possible email addresses in E2:E100, use =COUNTIF(E2:E100,"") to count cells that appear empty. To count cells that contain something, use =COUNTIF(E2:E100,"<>"). If formulas in the range return an empty string, their treatment can be confusing when diagnosing a count; check the source formulas and verify the result against a few cells rather than assuming every visually empty cell is a truly empty cell.

Match Text with Wildcards

For text criteria, an asterisk (*) matches any number of characters, including none, while a question mark (?) matches one character. These wildcards are handy when a cell contains extra text or when you know only part of an entry.

With product descriptions in A2:A100, count cells that start with the word Desk using =COUNTIF(A2:A100,"Desk*").
With email addresses in B2:B100, count addresses ending in the example domain example.com using =COUNTIF(B2:B100,"*example.com").
With codes in C2:C100, count three-character entries beginning with A using =COUNTIF(C2:C100,"A??").

If you need to search for an asterisk or question mark as a literal character rather than as a wildcard, put a tilde (~) before it in the criterion. For example, with notes in A2:A50, count cells containing a literal asterisk using =COUNTIF(A2:A50,"*~**"). The asterisks at the beginning and end allow other text around the literal asterisk in the cell. Wildcards apply to text matching; for ordinary number comparisons, use comparison criteria instead.

Count with More Than One Condition

COUNTIF accepts one criterion. If the answer depends on two or more conditions at the same time, use COUNTIFS, which applies each criterion to its corresponding range. For example, suppose regions are in A2:A100 and order statuses are in B2:B100. To count rows where the region is North and the status is Paid, use:

=COUNTIFS(A2:A100,"North",B2:B100,"Paid")

The ranges in a COUNTIFS formula should cover the same rows so that each region is tested alongside the status on the same row. Each additional range is followed by its own criterion. COUNTIFS uses AND logic: a row must meet every listed condition to be counted. It is therefore not the same as counting cells that match one condition or another. For an either/or question, add separate COUNTIF results when the categories cannot overlap, or construct the logic carefully if a record could satisfy both conditions.

Fix Common COUNTIF Problems

If a result is unexpectedly zero or too large, check the following before changing the formula:

Look for extra spaces: A cell containing Paid followed by a space may not match an exact criterion of Paid. TRIM can clean imported text, or you can use a wildcard criterion when a partial match is appropriate.
Check the range: Confirm that the formula includes the data rows and does not accidentally include a header or omit new entries.
Check the criterion type: Text criteria need quotation marks in the formula, while a cell reference is normally entered without quotation marks. Comparison operators such as >= must be combined with the threshold when that threshold comes from a cell.
Consider number versus text: A number stored as text may behave differently from a numeric value in comparisons. Check the cell’s underlying value and formatting if a numeric test seems wrong.
Watch for locale separators: Google Sheets usually shows commas between arguments in many locales, but spreadsheet locale settings can require semicolons. If Sheets reports a formula parse error, check File > Settings and use the separator expected by that sheet.
Understand case matching: COUNTIF does not distinguish uppercase from lowercase. If capitalization must matter, COUNTIF alone is not the right test.

For a range that grows over time, you can use a whole-column reference. With statuses anywhere in column B, this counts Paid entries in that column:

=COUNTIF(B:B,"Paid")

A whole-column range is convenient, but a bounded range can make the intended data area clearer and avoid counting unrelated values elsewhere in the column. If you copy a formula and want the range to stay fixed, use dollar signs in the reference. For example, with statuses in B2:B100 and different criteria in E2:E10, enter the formula below in F2 and fill it down:

=COUNTIF($B$2:$B$100,E2)

COUNTIF Compared with Other Options

COUNTIF is the simplest choice when you need one count based on one rule. Use COUNTIFS when multiple conditions must be true for the same row. Use a filter when you want to inspect or work directly with matching records rather than only see a total. Conditional formatting is useful when the goal is to highlight qualifying cells visually; in Google Sheets, its menu path is Format > Conditional formatting.

For a one-off count, a formula is often more convenient than manually counting visible matches because it recalculates as the data changes. For recurring reports, keep the criterion in a cell instead of embedding it in the formula, so another person can change the target without editing the formula itself. COUNTIF does not ignore errors in a special, user-controlled way; if errors occur in the range, inspect them as part of checking unexpected results.

Frequently Asked Questions

Can COUNTIF count cells that contain a word anywhere in the text?
Yes. Use an asterisk before and after the word in the criterion. With comments in A2:A100, =COUNTIF(A2:A100,"*late*") counts cells containing late anywhere in their text. The match is not case-sensitive.

Does COUNTIF distinguish uppercase and lowercase?
No. A criterion of Paid also matches PAID or paid. If your task requires case-sensitive counting, use a different formula approach, because COUNTIF itself does not provide a case-sensitive option.

How do I count dates before or after a date?
Dates in Sheets are stored as values that can be compared. With dates in A2:A100 and a cutoff date in E1, use =COUNTIF(A2:A100,"<"&E1) to count dates earlier than the cutoff. Check that the cells contain actual dates, not date-looking text.

Can I use COUNTIF across another sheet?
Yes. With statuses in B2:B100 on a sheet named January Sales, use =COUNTIF('January Sales'!B2:B100,"Paid"). The single quotes around a sheet name are needed when it contains spaces or certain special characters.

Why does COUNTIF return zero when the value looks correct?
Common causes include extra spaces, a number stored as text, a criterion with mismatched punctuation, or a range that points to the wrong cells. Check the raw cell contents and try a small range containing one known match to isolate the issue.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.