Google Sheets COUNTIF Criteria: Syntax and Examples

In Google Sheets, a COUNTIF criterion is the condition that tells the function which cells to count. Use =COUNTIF(range, criterion): for example, with order statuses in B2:B20, =COUNTIF(B2:B20,"Paid") counts cells whose value is Paid.

COUNTIF syntax and what the criterion means

COUNTIF checks each cell in a range against one condition and returns the number of cells that meet it. The first argument is the range to inspect; the second is the criterion. The criterion can be text, a number, a comparison expression, a wildcard pattern, a blank-cell test, or a reference to a cell containing a condition.

For a basic example, suppose B2:B20 contains order statuses and exactly six cells contain Paid. The formula =COUNTIF(B2:B20,"Paid") returns 6. Text criteria go inside double quotation marks. A numeric criterion can be supplied directly, such as =COUNTIF(C2:C20,100), which counts cells equal to 100. The range and criterion are separated by a comma in the standard Google Sheets locale.

COUNTIF uses a single condition. It does not add up values; it counts matching cells. If you need to total the values that meet a condition, SUMIF is usually the relevant function. If the requirement has multiple conditions, COUNTIFS is the corresponding counting function.

Count exact text and numbers

Text matching is usually exact in content: a criterion of Paid does not mean “contains Paid.” It matches cells whose text is Paid, rather than values such as Unpaid or Paid late. Matching is not case-sensitive, so Paid, PAID, and paid count as the same text for COUNTIF.

Assume A2:A8 contains the entries North, South, North, East, North, South, and West. The formula =COUNTIF(A2:A8,"North") returns 3. If A2:A8 instead contains the numbers 4, 7, 7, 10, 12, 7, and 15, =COUNTIF(A2:A8,7) returns 3. A number stored as text may not behave like a genuine numeric value in every comparison, so check the underlying cell values if a result is unexpectedly zero.

To match a text value held in another cell, use the cell reference as the criterion rather than putting the reference in quotation marks. Suppose F2 contains North and A2:A8 contains the region names above. The formula =COUNTIF(A2:A8,F2) returns 3. Writing "F2" would ask Sheets to find the literal text F2 instead.

Use comparison operators for numeric criteria

To count values above, below, or equal to a threshold, put the comparison operator and value together in quotation marks. Supported comparison operators include greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>).

Suppose C2:C6 contains 45, 100, 125, 90, and 210. The formula =COUNTIF(C2:C6,">=100") returns 3, because 100, 125, and 210 meet the condition. The formula =COUNTIF(C2:C6,"<>100") returns 4 because it counts all values other than 100. When the comparison operator is part of the criterion, it must be included in the quoted criterion text.

For a threshold that changes, place the threshold in a cell and join the operator to that cell reference. If C2:C6 contains the five values above and F2 contains the number 100, =COUNTIF(C2:C6,">"&F2) returns 2: only 125 and 210 are greater than 100. The ampersand joins the text operator to the value in F2. This is useful for dashboards or reports where a reader may adjust a cutoff without editing the formula.

Match partial text with wildcards

Google Sheets COUNTIF supports wildcards in text criteria. An asterisk (*) represents any number of characters, including none; a question mark (?) represents exactly one character. These are useful for counting categories that share a word or a text pattern.

Assume A2:A6 contains North, Northeast, South, Northwest, and West. The formula =COUNTIF(A2:A6,"North*") returns 3 because each of the three matching entries begins with North. The criterion "*west" would match text ending in west, while "N?rth" matches a five-character pattern with any one character in the question-mark position.

If the text you need to match contains an actual asterisk or question mark, put a tilde (~) before the wildcard character to treat it literally. For example, the criterion "~*" looks for an asterisk character rather than using it as a pattern. Wildcards apply to text matching; they are not a general substitute for regular expressions.

Count dates and blank cells

Dates in Sheets are stored as serial values, so COUNTIF can compare them with the same operators used for numbers. Use a real date value in the cells, and make sure the criterion is constructed as a date comparison rather than relying on an ambiguous text string. Formatting a cell to look like a date does not necessarily turn text into a date value.

Suppose A2:A6 contains five actual dates, and D2 contains a valid date representing January 1, 2026. The formula =COUNTIF(A2:A6,">="&D2) counts the dates on or after the date in D2. Its result depends on the dates in A2:A6; dates equal to the threshold are included. This cell-reference approach also avoids hard-coding a date whose interpretation might vary by locale.

To count blank-looking cells in A2:A20, use =COUNTIF(A2:A20,""). To count cells that are not blank, use =COUNTIF(A2:A20,"<>"). Be aware that a cell containing a formula that returns an empty string may look blank even though it contains a formula. If blank counts seem surprising, inspect the cells and consider whether the range includes formulas or spaces. A cell containing a space is not the same as an empty cell.

Case sensitivity and common limitations

COUNTIF text matching is case-insensitive. It cannot distinguish North from NORTH by changing the criterion’s capitalization. If capitalization matters, a different approach involving case-sensitive text comparison is needed; COUNTIF alone is not designed to enforce that distinction.

Another limitation is that COUNTIF accepts one condition for each evaluated row. A request such as “count orders from North with a value above 100” involves two conditions, so a single COUNTIF is not the right tool. Also, a criterion such as ">100" is a comparison condition, whereas "100" asks for a match to the value 100; leaving out the operator changes the meaning.

In the standard Google Sheets locale, function arguments are separated with commas. Some spreadsheet locales use semicolons instead, so a formula copied from another region may need its separators changed. The function name remains COUNTIF. Basic COUNTIF syntax and wildcard conventions are also broadly familiar to Excel users, although separators and other regional settings can differ. This page describes Google Sheets in a web browser.

Choose COUNTIFS when conditions combine

Use COUNTIFS when you need to apply two or more conditions, such as matching a region and a minimum sales amount. Each condition has its own range and criterion, and the ranges should correspond in size so that the conditions are evaluated against the same rows.

Suppose A2:A6 contains North, South, North, East, and North, while B2:B6 contains 120, 140, 90, 200, and 180. The formula =COUNTIFS(A2:A6,"North",B2:B6,">=100") returns 2: the first and fifth rows are North and have values of at least 100. The third North row is excluded because its value is 90. This differs from using COUNTIF twice, which would give separate counts rather than directly count rows meeting both conditions.

For “this condition or that condition,” COUNTIFS may not be the cleanest way to express the task, because its conditions are combined as an AND test. Depending on the exact data and whether categories can overlap, you may add separate COUNTIF results or use another suitable formula. Think through whether the intended logic is AND (all conditions must be true) or OR (any condition may be true) before choosing a function.

Fix a COUNTIF result that looks wrong

When COUNTIF returns zero or an unexpected total, first verify the selected range and the exact criterion. A range that omits the newest rows, a hidden trailing space in a category, or a criterion accidentally enclosed in quotes as a cell address can all cause mismatches. Comparing a text number with a numeric value can also produce a different result than expected.

Check that the range contains the cells you intend to count and does not include an unrelated header.
Confirm that quotation marks are straight double quotes and that comparison operators are inside the criterion string.
For a criterion stored in a cell, reference the cell directly; do not quote the reference as literal text.
Look for leading or trailing spaces, values stored as text, and formulas that display an empty string.
If the criterion uses a wildcard, check whether the asterisk or question mark is acting as a wildcard or should be escaped with a tilde.

For a quick diagnostic, temporarily count a simple exact value that you can see in the range, then add the comparison or wildcard once that basic match works. If data comes from a form or an imported file, cleaning inconsistent spaces or standardizing values may be more reliable than building increasingly complicated criteria.

Frequently Asked Questions

Does Google Sheets COUNTIF need quotation marks?
Text criteria and comparison criteria are normally written in double quotation marks, such as "Paid" or ">=100". A numeric criterion can be supplied as a number without quotes, and a cell reference can be used directly as the criterion.

How do I count cells that contain a word?
Use asterisks around the word to match it within other text. For example, the criterion "*late*" matches text containing late anywhere in the cell. COUNTIF matching is case-insensitive.

Can COUNTIF use more than one condition?
No. COUNTIF evaluates one criterion. Use COUNTIFS when a row must satisfy multiple conditions, such as a matching region and a value above a threshold.

How do I count cells that are not blank?
Use the not-equal-to-blank criterion "<>" with the range you want to check. If a cell appears empty but contains a formula returning an empty string, check that formula when interpreting the count.

Is COUNTIF case-sensitive in Google Sheets?
No. COUNTIF treats uppercase and lowercase versions of matching text as equivalent. Changing the case of the criterion will not make it count only one capitalization.

Why does my COUNTIF formula use semicolons?
Google Sheets can use different argument separators depending on locale settings. If your sheet expects semicolons, use those between the function arguments; formulas copied from a comma-based locale may need adjustment.

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.