COUNTIF in Google Sheets: Syntax and Examples

In Google Sheets, COUNTIF counts cells in a range that meet one condition. With product names in A2:A20, for example, enter =COUNTIF(A2:A20,"Apple") to count cells whose value is Apple; the function ignores capitalization when matching text.

What COUNTIF Does

COUNTIF is useful when you want a count rather than a list or a sum. You can use it to count how many orders have a particular status, how many scores meet a threshold, or how many records contain a word or date. The range can be a column, a row, or a rectangular group of cells.

Its two inputs have distinct jobs: the range identifies the cells to inspect, and the criterion tells Sheets what to look for. COUNTIF checks each cell against that criterion and returns one number. It does not change the source data, and it updates when the cells or criterion change.

A useful limitation to know up front is that COUNTIF checks one condition at a time. For example, it can count orders marked Paid, but it cannot by itself count orders that are both Paid and from a particular region. For multiple conditions, use COUNTIFS.

Syntax and the Criterion Argument

With the cells to check in A2:A20, the range argument is A2:A20. The second argument is the criterion: it may be a number, text, a comparison written as text, a wildcard pattern, or a reference to another cell. Put text and comparison criteria in straight double quotation marks.

For a simple exact text match, the first example uses Apple as the criterion. Text matching is not case-sensitive, so Apple and APPLE match the same way. Spaces do matter: a cell containing Apple followed by a space may not match the clean word Apple.

Comparison operators include greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), not equal to (<>), and equal to (=). When a comparison includes a value directly, put the operator and value together inside the quoted criterion. When the value comes from a cell, join the operator to that cell reference with an ampersand.

Google Sheets uses commas between arguments in many spreadsheet locales, as in the examples here. Depending on the file’s locale, formulas may instead require semicolons as argument separators. If a formula produces a parse error despite having balanced parentheses and quotation marks, check File > Settings for the spreadsheet locale.

Basic Examples for Text and Numbers

Suppose A2:A8 contains Apple, Banana, Apple, Orange, Apple, a blank cell, and Bananas. With those values in A2:A8, use the following formula to count the exact word Apple. It returns 3; Bananas is not an exact match for Banana.

With the example product list in A2:A8, enter =COUNTIF(A2:A8,"Apple").

COUNTIF can also evaluate numeric comparisons. Suppose B2:B8 contains 12, 18, 7, 22, 18, 0, and a blank cell. With those scores in B2:B8, the next formula counts values of 18 or more. The result is 3: two cells contain 18 and one contains 22.

With scores in B2:B8, enter =COUNTIF(B2:B8,">=18").

For a criterion stored in a cell, joining the operator and reference makes the test flexible. If the scores are in B2:B8 and the minimum threshold is in D1, changing D1 changes the count without editing the formula.

With scores in B2:B8 and a threshold in D1, enter =COUNTIF(B2:B8,">="&D1).

Using Wildcards for Partial Text Matches

Wildcards let you count text that contains a pattern rather than an exact word. In a COUNTIF criterion, an asterisk (*) represents any number of characters, including none. A question mark (?) represents exactly one character. These are handy for matching name endings, prefixes, or text with a variable character.

*son matches text ending in son, such as Jackson.
North* matches text beginning with North, including North and Northeast.
AB-?? matches AB- followed by exactly two characters.

If A2:A10 contains names, the following pattern counts entries ending in son. The formula counts every matching cell, regardless of capitalization.

With names in A2:A10, enter =COUNTIF(A2:A10,"*son").

To search for a literal asterisk or question mark rather than use it as a wildcard, precede it with a tilde (~) in the criterion. For example, the pattern ~* means a literal asterisk. This distinction matters when a column contains codes or notes that use wildcard characters as actual data.

Counting Blanks, Nonblanks, and Dates

A blank criterion can help identify missing entries. If A2:A20 contains a list of contact details, counting blank cells can show how many records still need information. A criterion written as not equal to blank is commonly used to count populated cells, though a cell containing spaces is not truly empty.

With contact details in A2:A20, use the following formula to count blank cells. Its result depends on the current contents of that range.

With contact details in A2:A20, enter =COUNTIF(A2:A20,"").

Dates are stored as numeric values in spreadsheets, so COUNTIF can test them with comparison criteria. For a date range, a pair of limits is often clearer than trying to match a date displayed in a particular format. COUNTIFS can count dates on or after the first day of a month and before the first day of the next month, which includes every date in that month regardless of the day.

With dates in B2:B100, use this formula to count dates in January 2026. The date limits are created with DATE, so the test does not depend on whether the cells display dates as 1/5/2026, 5-Jan-2026, or another date format.

With dates in B2:B100, enter =COUNTIFS(B2:B100,">="&DATE(2026,1,1),B2:B100,"<"&DATE(2026,2,1)).

COUNTIF or COUNTIFS?

Choose COUNTIF when one test is enough. Choose COUNTIFS when each row must meet two or more tests. COUNTIFS takes pairs of ranges and criteria, and the corresponding ranges should cover the same number of rows or columns. This makes it suitable for questions such as “How many orders are Paid and from the West region?”

Suppose order status is in A2:A20 and region is in B2:B20. With those layouts, the formula below counts rows where the status is Paid and the region is West. Both conditions must be true for a row to contribute to the count.

With statuses in A2:A20 and regions in B2:B20, enter =COUNTIFS(A2:A20,"Paid",B2:B20,"West").

COUNTIFS is not simply a longer version of COUNTIF: its criteria are combined as an AND test across the listed ranges. If you need an OR result, such as counting either Paid or Pending, you can add separate COUNTIF results when the categories cannot overlap. If categories could overlap, take care not to count the same record twice.

Common Problems and How to Fix Them

The count is zero unexpectedly: Check for extra spaces, a misspelled criterion, or a criterion that requires an exact match when you intended a partial match. Wildcards can help with partial text, but they do not remove unwanted spaces from the source cells.
A comparison is treated as text: Make sure the operator and value form one quoted criterion, such as ">=18". For a value in another cell, join the quoted operator and the cell reference rather than placing the reference inside quotation marks.
The formula shows a parse error: Confirm that quotation marks and parentheses are paired. Also check whether the spreadsheet locale expects semicolons instead of commas between arguments.
The result changes when copied: Relative references move when a formula is copied. If the range should stay fixed, use absolute references such as $A$2:$A$20; if the criterion should move by row, leave that reference relative.
Numbers or dates do not match: Verify that the cells contain numeric values or real dates rather than text that only looks numeric or date-like. Number formatting changes display, but it does not convert text into a number.

COUNTIF is case-insensitive, so it is not the right tool when uppercase and lowercase must count as different values. Also remember that a cell with a space is nonblank, even if it appears empty at a glance. Cleaning source data or using a more specialized formula may be necessary when the count must distinguish those cases.

Frequently Asked Questions

Is COUNTIF case-sensitive in Google Sheets?
No. COUNTIF treats uppercase and lowercase versions of the same text as equivalent. If capitalization must distinguish matches, use a case-sensitive approach rather than relying on COUNTIF alone.

Can COUNTIF count cells that contain a word anywhere?
Yes. Use an asterisk on both sides of the word in the criterion to allow other text before and after it. For example, with descriptions in C2:C50, the pattern *late* can match cells containing late as part of a longer entry.

How do I count cells greater than a value in another cell?
Join a quoted comparison operator to the cell reference with an ampersand. With scores in B2:B20 and a threshold in D1, the pattern is =COUNTIF(B2:B20,">"&D1).

Can COUNTIF check two conditions?
No. COUNTIF accepts one criterion. Use COUNTIFS when a result must satisfy multiple conditions, such as matching both a status and a region.

Does COUNTIF work in Excel too?
Yes. COUNTIF is also available in Excel, and its basic syntax is similar. The spreadsheet locale can affect whether formulas use commas or semicolons as separators, and some interface details differ between Google Sheets and Excel.

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.