COUNTIF in Google Sheets: Syntax, Examples, and Tips

In Google Sheets, use COUNTIF(range, criterion) to count cells in a range that meet one condition. For example, if A2:A20 contains region names, =COUNTIF(A2:A20,"East") counts the cells whose value is East.

What COUNTIF does

COUNTIF is a function for answering questions such as “How many orders are marked Shipped?” or “How many scores are at least 80?” It examines each cell in a specified range, checks it against one criterion, and returns a single number. The count updates when values in the range change, so it can serve as a live summary alongside a table.

The function has two arguments: the range to inspect and the criterion to apply. A criterion may be an exact text value, a number, a comparison such as greater than 10, or a wildcard pattern. The criterion must be supplied in a form Sheets can interpret; comparison operators and text criteria are commonly written inside quotation marks.

To enter the function, select an empty cell, type the formula beginning with an equals sign, and press Enter. Google Sheets displays the result in that cell. The examples below assume the sheet uses commas to separate formula arguments, as in the standard US-style syntax. Spreadsheet locale settings can affect separators and how dates or decimal values are entered.

Basic syntax and exact matches

Start with a straightforward exact-match count. If A2:A20 contains region names, =COUNTIF(A2:A20,"East") counts occurrences of East in that range. The range can be a column segment, a row segment, or a rectangular group of cells. Use a specific range when you know where the data is; it makes the formula easier to audit and avoids scanning irrelevant cells.

Criteria can also refer to a cell rather than typing the match value directly. If region names are in A2:A20 and the value to search for is in D2, =COUNTIF(A2:A20,D2) counts matches for whatever is currently in D2. This is useful for a small report where a reader can change the search term without editing the formula.

Text matching with COUNTIF is not case-sensitive: East and EAST are treated as the same criterion. Extra spaces are different, however. A cell containing “East ” with a trailing space may not match “East.” If counts seem unexpectedly low, check the underlying values for spaces or inconsistent labels before changing the formula.

A range on another tab can be counted by naming the tab and range. If the tab is named Order Log and its status values are in C2:C500, =COUNTIF('Order Log'!C2:C500,"Shipped") counts Shipped entries there. Single quotes around a sheet name are needed when it contains spaces or certain special characters; using them consistently is also fine for simple names.

Using comparison criteria for numbers

For numeric conditions, put the comparison operator and value together in a quoted criterion. If B2:B20 contains quantities, =COUNTIF(B2:B20,">10") counts values greater than 10, while =COUNTIF(B2:B20,"<=10") counts values less than or equal to 10. Common operators are greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>).

When a cutoff is stored in a cell, join the operator to that cell with an ampersand. If quantities are in B2:B20 and the cutoff is in D2, =COUNTIF(B2:B20,">"&D2) counts quantities above the value in D2. This makes a threshold easy to change. The ampersand combines the operator text with the cell’s value so Sheets can read the combined result as a criterion.

Make sure values intended to be numbers are actually numeric. A number stored as text can behave differently from a numeric value in comparisons, particularly when data was imported or copied from another system. Check number formatting and source data if a comparison count does not agree with a manual review.

Wildcards for partial text matches

Wildcards let COUNTIF match text without requiring the entire cell value to be identical. In a criterion, an asterisk (*) represents any sequence of characters, including no characters; a question mark (?) represents one character. For instance, if A2:A50 contains product labels, =COUNTIF(A2:A50,"Pro*") counts labels that begin with Pro, such as Pro, Product, or Professional.

To search for text anywhere within a cell, put an asterisk on both sides of the text. If A2:A50 contains descriptions, =COUNTIF(A2:A50,"*late*") counts cells containing the character sequence late, whether it appears at the beginning, middle, or end. This can match words such as “late” and “related,” so choose a distinctive search fragment when false matches matter.

The tilde (~) escapes a wildcard when you want to find the wildcard character itself. If A2:A50 contains notes with literal asterisks, =COUNTIF(A2:A50,"~*") counts cells matching a literal asterisk rather than treating it as a wildcard. Wildcards are for text matching; they are not a substitute for numeric comparisons.

Counting dates and date ranges

Google Sheets stores valid dates as numeric values, so COUNTIF can use comparison criteria to count dates before, after, or on a cutoff. If dates are in C2:C100, =COUNTIF(C2:C100,">="&DATE(2026,1,1)) counts dates on or after January 1, 2026. Using DATE(year, month, day) avoids relying on how a typed date string is interpreted under a particular locale.

To count dates within a month, use COUNTIFS, which applies more than one condition. If timestamps or dates are in C2:C100, =COUNTIFS(C2:C100,">="&DATE(2026,1,1),C2:C100,"<"&DATE(2026,2,1)) counts values from January 1 up to, but not including, February 1, 2026. The less-than boundary is useful when the cells include times as well as dates: a timestamp on January 31 still falls before February 1.

If dates are stored as text rather than recognized date values, date comparisons may not work as expected. Apply a date format or convert the source data to real dates before relying on the count. Formatting changes how valid dates display; it does not necessarily convert arbitrary text into date values.

More than one condition: COUNTIFS and OR logic

COUNTIF handles one condition. When a count must satisfy two or more conditions at the same time, use COUNTIFS. For example, if department names are in A2:A100 and statuses are in B2:B100, =COUNTIFS(A2:A100,"Sales",B2:B100,"Open") counts rows where the department is Sales and the status is Open. Each additional condition is supplied as a range-and-criterion pair.

With COUNTIFS, the condition ranges should correspond to the same set of rows and have matching dimensions. If the department range starts on row 2 but the status range starts on row 3, the formula compares misaligned records and can produce a misleading result. Keep range boundaries consistent, especially when copying formulas into a summary table.

For an “either/or” count using one column, add two COUNTIF results. If B2:B100 contains one status per row, =COUNTIF(B2:B100,"Open")+COUNTIF(B2:B100,"Pending") counts rows labeled Open or Pending. This is appropriate when each cell can contain only one of those labels. If criteria can overlap or the data contains multiple labels in one cell, adding separate counts may count a row more than once; the calculation should reflect the structure of the data.

Practical setup and troubleshooting

A dependable workflow is to identify the data range, decide what counts as a match, enter the formula in a summary cell, and compare the result against a few known records. If you are building a reusable report, keep search terms or thresholds in separate input cells and point the criterion to those cells. Clear labels next to the result make it easier for other sheet users to understand what is being counted.

Use a consistent data range for related totals, such as A2:A500 rather than accidentally including a heading in one formula.
Check for leading or trailing spaces, different spellings, and values stored as text when a count is too low.
Inspect the criterion for missing quotation marks around text or comparison expressions.
For date counts, confirm that the source cells contain actual dates and use a half-open interval for dates that may include times.
Use COUNTIFS when every counted row must meet multiple conditions; do not try to combine independent conditions into one COUNTIF criterion.

COUNTIF counts cell values, not formatting. It cannot directly answer “How many cells have a yellow background?” or “How many cells use bold text?” without a different approach, such as restructuring the data to record the relevant status in a column. Also, a formula can return a correct count for the chosen range while still missing newly appended rows. If new data is added regularly, choose a range that accommodates it or update the range as needed. Very broad ranges may be convenient, but can add unnecessary work in a large sheet.

COUNTIF compared with related functions

COUNTIF is the simplest choice when there is one condition. COUNTIFS is the natural extension for multiple AND conditions, such as a particular product and a particular month. COUNTA counts non-empty cells regardless of their values, while COUNTBLANK counts blank cells. Those functions answer different questions: for example, “How many records have an entry?” is not the same as “How many records are marked Approved?”

Use a filter when the goal is to inspect or temporarily display the matching records rather than only report a number. In Google Sheets, the Data menu includes filter options for working with visible rows. A filter does not replace COUNTIF in a dashboard where a single live total is needed, but it can help verify which records meet the condition. COUNTIF itself is built into Sheets and does not require an add-on.

Excel also has a COUNTIF function with broadly similar syntax, but this page describes Google Sheets in a web browser. Interface details and some formula behavior can differ between spreadsheet products, so use the function help and menus for the program you are actually using. In Sheets, formulas are entered directly into cells; they can also be edited in the formula bar above the grid.

Frequently Asked Questions

How do I count cells that are not blank?
If values are in A2:A20, =COUNTIF(A2:A20,"<>") counts cells that are not empty. If the task is simply to count all non-empty cells in a range, COUNTA may be easier to read.

Can COUNTIF count partial words?
Yes. Use asterisks around the text fragment. With descriptions in A2:A50, =COUNTIF(A2:A50,"*urgent*") counts cells containing urgent anywhere in the text. The match is not case-sensitive.

How do I count values greater than a number in another cell?
If scores are in B2:B20 and the comparison value is in D2, use =COUNTIF(B2:B20,">"&D2). The operator is text joined to the cell value with an ampersand.

Why is COUNTIF returning zero when I can see a match?
Check that the range includes the cell, that the criterion is spelled and quoted correctly, and that the cell does not contain an extra space. Also verify that numbers or dates are stored as actual numeric values rather than text. A wildcard criterion can help when text varies around a common fragment.

Can COUNTIF distinguish uppercase from lowercase?
No. COUNTIF text matching is case-insensitive in Google Sheets. If the distinction between upper- and lowercase letters is important, use a case-sensitive approach built around functions such as EXACT rather than relying on COUNTIF alone.

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.