Google Sheets COUNTIF Format: Syntax, Criteria, Examples

In Google Sheets, COUNTIF uses the format =COUNTIF(range, criterion) to count cells that meet one condition. For example, if order statuses are in B2:B10, =COUNTIF(B2:B10,"Paid") counts the cells whose value is Paid.

What COUNTIF does

COUNTIF is a worksheet function for counting matching cells, rather than adding their values or changing how they look. It is useful for questions such as how many orders are paid, how many scores are at least 80, or how many product names contain a particular word. The function returns a number, including zero when nothing matches.

The function has two arguments: a range to inspect and a criterion that describes what counts as a match. The range is usually a row or column of cells. The criterion can be a number, text, comparison, wildcard pattern, or a reference to another cell. COUNTIF evaluates each cell in the range against that one criterion.

COUNTIF is not the same as conditional formatting. It does not count cells because they are bold, colored, or displayed with a particular number format. It looks at cell values and criteria. If the goal is to color matching cells, use conditional formatting; if the goal is to return a count, use COUNTIF.

Basic syntax and a first example

The argument order matters: put the cells to check first and the condition second. In Sheets, commas separate the arguments in the examples below. Depending on spreadsheet locale settings, a sheet may require semicolons instead of commas when entering formulas.

Suppose B2:B10 contains these nine statuses: Paid, Pending, Paid, Cancelled, Paid, Pending, Paid, Cancelled, and Paid. With those statuses in B2:B10, enter =COUNTIF(B2:B10,"Paid"). The result is 5 because Paid appears five times. Text criteria go inside straight double quotation marks.

A criterion that is a number can be entered without quotation marks. If C2:C10 contains 12, 18, 25, 31, 44, 52, 67, 73, and 90, then =COUNTIF(C2:C10,25) returns 1. To make a formula easier to maintain, you can also put a criterion in a cell and refer to that cell, which is especially helpful when the condition changes often.

Using comparison operators

To count values above, below, equal to, or different from a threshold, write the operator and threshold together as the criterion. In the same C2:C10 score range above, =COUNTIF(C2:C10,">=50") returns 4: the matching scores are 52, 67, 73, and 90. The comparison expression must be quoted because it is a text criterion that Sheets interprets as an operator followed by a value.

Common operators include > (greater than), >= (greater than or equal to), < (less than), <= (less than or equal to), = (equal to), and <> (not equal to). If the threshold is stored in a cell rather than typed into the formula, join the operator to the cell reference with an ampersand. For example, with the minimum score in E2 and scores in C2:C10, use =COUNTIF(C2:C10,">="&E2). If E2 contains 50, the result is 4.

When entering a formula directly in Sheets, type the ampersand character, not the HTML entity shown by a web page renderer. The ampersand combines the comparison text with the value in E2. This pattern also works for date thresholds and other numeric criteria.

Text criteria, wildcards, and case

For an exact text criterion, put the requested text in quotation marks, as in the Paid example. COUNTIF text matching is not case-sensitive, so Paid, PAID, and paid are treated as the same text for this purpose. It does not mean that every word or phrase in a cell must be matched: an exact text criterion ordinarily matches the whole cell value. Use wildcards when you want to match part of a text value.

Asterisk (*): matches any number of characters, including none. If product names are in A2:A20 and the goal is to count names containing tea anywhere, use =COUNTIF(A2:A20,"*tea*").
Question mark (?): matches one character. With codes in A2:A20, =COUNTIF(A2:A20,"AB?1") counts values such as ABX1 or AB71, but not AB121.
Tilde (~): treats the following wildcard character literally. To count cells containing an actual asterisk, use =COUNTIF(A2:A20,"~*").

A common surprise is that a wildcard criterion can match more than a reader expects. For example, *tea* can match both green tea and teapot because the pattern asks for those letters anywhere in the cell. If the intended match is a word at the start or end, place the asterisk only on the side where other characters are allowed. COUNTIF does not provide a case-sensitive text match; use a different approach if capitalization must distinguish otherwise identical text.

Counting blanks, nonblank cells, and dates

To count empty-looking cells in D2:D20, use =COUNTIF(D2:D20," ") only if the criterion is intended to be a literal space; that is different from an empty criterion. To count blank cells, use =COUNTIF(D2:D20,""). This can count genuinely empty cells and cells whose formulas return an empty string. That distinction matters when a column contains formulas that display nothing.

To count cells that are not blank, with data or formulas in D2:D20, use =COUNTIF(D2:D20,"<>"). A cell containing a space is not truly empty, so it can be counted as nonblank. If results seem off, inspect suspicious cells for invisible spaces or formulas that return an empty string.

Dates in Sheets are stored as serial values, even though they are usually displayed in a date format. Use a date-building function in the criterion rather than relying on how a date is visually written. If A2:A20 contains dates and the goal is to count dates on or after January 1, 2026, enter =COUNTIF(A2:A20,">="&DATE(2026,1,1)). This avoids ambiguity between date conventions such as month/day/year and day/month/year. The cells being counted must contain actual date values, not text that merely looks like a date.

When one condition is not enough

COUNTIF handles one criterion at a time. To require several conditions at once—for example, a particular status and a particular region—use COUNTIFS. It accepts pairs of ranges and criteria, and all the criteria must be satisfied for a row to count.

Suppose status is in B2:B20 and region is in C2:C20. To count records that are both Paid and West, use =COUNTIFS(B2:B20,"Paid",C2:C20,"West"). The status and region ranges should cover the same rows so the conditions are evaluated together. COUNTIFS is the natural choice for an AND condition; it is not simply a longer spelling of COUNTIF.

For an OR condition, add separate COUNTIF results when the categories are mutually exclusive. With statuses in B2:B20, =COUNTIF(B2:B20,"Open")+COUNTIF(B2:B20,"Pending") counts records whose status is Open or Pending. If the conditions can both match the same row—for example, overlapping wildcard patterns—adding the counts may count that row twice. In that case, use a method designed to avoid double counting or revise the criteria.

COUNTIF versus formatting and conditional formatting

The word “format” can refer either to the structure of the formula or to a cell’s appearance. COUNTIF has a clear formula format, but it does not read font color, fill color, bold style, or custom number formatting. A cell formatted to display 10 as $10.00 is still evaluated using its underlying value, not the visual symbols around it.

To visually highlight cells based on their contents, select the relevant cells and choose Format > Conditional formatting in Google Sheets. Choose a built-in rule or create a custom formula rule. For example, to highlight repeated names in A2:A, set the apply-to range to A2:A and use =COUNTIF($A$2:$A,$A2)>1 as the custom formula. The dollar signs keep the checked column fixed while the row reference adjusts for each cell. This rule highlights every occurrence of a name that appears more than once; it does not create a count summary.

If you need a count based on manually applied colors, a normal COUNTIF formula is not sufficient. Consider adding a category or status column that records the reason a cell has that color, then count the explicit value in that column. A data value is easier to filter, count, and maintain than formatting used as hidden information.

Common errors and ways to troubleshoot

Text is not quoted: criteria such as Paid need quotation marks when typed directly into a formula. Without them, Sheets may interpret the text as a named range or report an error.
The comparison is split incorrectly: put a comparison and its fixed value together inside quotes, such as “>=50”. When the threshold comes from a cell, join the quoted operator and reference with &.
The range is wrong: verify that the formula checks the column containing the values you intend to count. A shifted or partial range can return a plausible but incorrect total.
Values look alike but differ: extra spaces, nonprinting characters, or numbers stored as text can prevent a match. Check the source cells and consider cleaning or standardizing the data.
A wildcard matches too broadly: * matches any number of characters, while ? matches one. Remove or reposition wildcards to make the criterion more specific.
The formula separator differs: if Sheets reports a formula parse error, check the spreadsheet’s locale and whether the formula separator should be a comma or semicolon.

When a result is unexpected, test the criterion on a small, known range first. Confirm a few individual cells, then expand the range. For a changing list, a reference such as B2:B is convenient because it includes later rows, but a bounded range such as B2:B1000 can make the intended data area clearer. Choose a range that fits the sheet and its expected growth.

Frequently Asked Questions

What is the correct Google Sheets COUNTIF format?
Use =COUNTIF(range, criterion). The range identifies the cells to inspect, and the criterion specifies which values count. For example, if statuses are in B2:B10, =COUNTIF(B2:B10,"Paid") counts Paid entries.

How do I count cells that contain a word?
Use asterisks around the word as wildcards. If descriptions are in A2:A20, =COUNTIF(A2:A20,"*late*") counts cells containing the letters late anywhere in the text. This can match longer words too, not just the standalone word late.

Can COUNTIF count two conditions?
No. COUNTIF takes one criterion. Use COUNTIFS for multiple conditions that must all be true, such as a matching status and region. For an OR between separate, non-overlapping categories, add separate COUNTIF results.

Does COUNTIF count by cell color?
No. COUNTIF evaluates values and criteria, not fill color or font color. Use Format > Conditional formatting to color cells based on rules, or store the category you want to count in a dedicated data column.

Why does COUNTIF return zero when the text looks identical?
Check for extra spaces, hidden characters, a mismatched range, or text stored differently from the criterion. COUNTIF ignores capitalization, but that does not remove spaces or make unrelated values equivalent. Wildcards can help when a partial match is intended.

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.