Google Sheets COUNTIF Range: Syntax, Examples, and Fixes

In Google Sheets, COUNTIF counts cells in a range that meet one condition. The basic formula is =COUNTIF(A2:A20,"Open"); it counts cells in A2:A20 whose value matches Open. Change the range and criterion to fit your data, and use COUNTIFS when you need to apply more than one condition.

What COUNTIF means in Google Sheets

COUNTIF is a counting function: it examines cells in a range and returns how many satisfy a criterion. It is useful for quick summaries such as the number of orders marked Shipped, scores above a threshold, or entries containing a particular word. It returns a number, not the matching cells themselves.

The function has two arguments: the range to examine, followed by the criterion to test. The range is usually written as a cell reference such as A2:A20. A colon means the range includes every cell between its endpoints. You can select the cells with the mouse while editing the formula, or type the reference directly.

For example, suppose a task list has status values in B2:B40. If 11 cells contain Done, a COUNTIF formula targeting that range and criterion returns 11. The result changes when cells in the range change, so you do not need to recount the list manually.

Choosing the right range

A COUNTIF range should include the cells that hold the values you want to test, and not an unrelated heading or summary. If the heading in A1 is Status and the records begin in row 2, use A2:A rather than A:A when practical. Including a heading often will not affect a criterion such as Done, but excluding it makes the formula’s intent clearer and avoids accidental matches.

For a fixed report, a bounded range such as A2:A500 is easy to inspect. For a list expected to grow, a whole-column reference such as A2:A can include future entries without editing the formula. Large, repeated calculations over entire columns can be less efficient than bounded ranges, especially in a busy workbook, so choose a range appropriate to the sheet’s size.

COUNTIF also works with ranges on another tab. If the tab is named Sales Data, its reference needs single quotation marks because the name contains a space. A reference on the same spreadsheet uses the tab name and an exclamation mark before the cells. When selecting a range on another tab while entering a formula, Sheets can insert the reference for you.

Use one column when counting categories, names, or statuses.
Use one row when checking values across a row of periods or categories.
Use a rectangular range when each cell in that rectangle should be checked.
Keep the start and end cells accurate; a range that stops early silently leaves later records out.

How criteria work

The criterion can be text, a number, a comparison, a wildcard pattern, or a reference to another cell. Text criteria are usually written inside double quotation marks. COUNTIF does not distinguish uppercase from lowercase, so Open and OPEN match the same text. It does, however, compare the cell contents, not the visual formatting: a cell that looks like a date or number should contain a value that Sheets can interpret as one.

To count values that meet a numeric threshold, put the comparison operator and value together in the criterion. For instance, the criterion can mean greater than or equal to 50. If the threshold is stored in another cell, join the operator to that cell reference with an ampersand. This lets a user change the threshold without editing the formula.

With scores in B2:B31, and 12 scores at least 50, use =COUNTIF(B2:B31,">=50"). The result is 12. If the cutoff is in D1 and is 50, use =COUNTIF(B2:B31,">="&D1); it also returns 12 for the same data. In HTML, the displayed > and & entities represent the greater-than sign and ampersand in the formula.

Dates can be counted with comparison criteria too. With dates in C2:C100 and eight dates on or after January 1, 2026, use =COUNTIF(C2:C100,">="&DATE(2026,1,1)). The result is 8, assuming those are the qualifying dates in the range. DATE constructs a date value, avoiding the ambiguity of typing a date as text; the spreadsheet’s locale still affects how dates are entered and displayed.

Useful COUNTIF examples

Assume a basic task tracker has status values in A2:A20. If six cells contain Open, the formula =COUNTIF(A2:A20,"Open") returns 6. COUNTIF ignores letter case, so entries such as open and OPEN are included in that total. The range includes only the status cells, not the column label.

Wildcards let you count text that is not an exact match. An asterisk represents any number of characters, including no characters, and a question mark represents one character. With product descriptions in A2:A50, if nine descriptions contain the word north anywhere, =COUNTIF(A2:A50,"*north*") returns 9. A criterion of north followed by an asterisk would count values beginning with north, while an asterisk followed by north would count values ending with it.

Wildcards are helpful for text searches, but they can surprise you when the character itself is part of the data. A tilde escapes a wildcard. With literal asterisk entries in A2:A20, if exactly two cells contain just an asterisk, =COUNTIF(A2:A20,"~*") returns 2. The tilde tells Sheets to treat the following asterisk as a character to find, rather than as a pattern.

To count blank cells in A2:A20, use =COUNTIF(A2:A20,""). If five cells are blank, the result is 5. This criterion can also match formulas that return an empty string, which may look blank even though the cell contains a formula. That distinction matters when a sheet uses formulas to hide unused rows.

When to use COUNTIFS instead

COUNTIF tests one condition. If you need to count rows that meet two or more conditions, use COUNTIFS, which accepts a criteria range and criterion for each condition. For example, it can count records whose status is Closed and whose region is West. The criteria ranges should cover corresponding records and have the same dimensions; mismatched ranges can produce an error or an unintended result.

With status values in A2:A100, regions in B2:B100, and 14 rows where status is Closed and region is West, use =COUNTIFS(A2:A100,"Closed",B2:B100,"West"). It returns 14. Each row must meet both conditions to count. COUNTIFS therefore behaves like an AND test across its condition pairs.

If you want to count either of two alternatives in one column, separate COUNTIF calculations can be added. With status values in A2:A100, suppose there are 18 Open entries and 7 Pending entries, and no cell can contain both labels. The formula =COUNTIF(A2:A100,"Open")+COUNTIF(A2:A100,"Pending") returns 25. If the alternatives can overlap, adding the counts may count some records twice; consider the data structure before summing.

Common problems and how to fix them

A result of zero usually means the criterion does not match the underlying values, or the selected range is wrong. Check for extra spaces, spelling differences, and numbers stored as text. A cell may display 50 while containing text rather than a numeric 50, so numeric comparisons may not behave as expected. Also confirm that the range includes the rows where the matching values actually appear.

If a formula gives a formula parse error, inspect its commas, parentheses, quotation marks, and range references. Google Sheets uses the spreadsheet’s locale to determine formula separators; some locales use semicolons rather than commas. The examples here use commas, which are standard in many English-language locales. If a formula from a different locale is pasted into your sheet and rejected, check the locale-specific separator.

For a result that changes unexpectedly, inspect whether the range is expanding or whether a referenced threshold cell has changed. A normal reference such as A2:A20 remains pointed to those cells when copied in some contexts; dollar signs can lock a reference when copying a formula. For example, a criterion reference may need to remain fixed while the range is filled down. Test a formula on a small, known set of rows before using it in a summary.

COUNTIF is not case-sensitive. If uppercase and lowercase versions must be counted separately, COUNTIF alone is not the right tool. With A2:A20 containing text and exactly three cells matching Apple with the same capitalization, =SUMPRODUCT(--EXACT("Apple",A2:A20)) returns 3. EXACT checks case-sensitive equality, and SUMPRODUCT totals the resulting matches. Verify the range and expected capitalization before relying on this less familiar approach.

COUNTIF compared with alternatives

Use COUNTIF when one condition describes the cells to count. Use COUNTIFS when each qualifying record must meet several conditions. Use a filter when you want to inspect or temporarily display the matching rows rather than just report a count. These approaches solve related but different tasks: a count is compact for a summary, while filtering is better for reviewing individual records.

If you need case-sensitive matching, a COUNTIF formula will not provide it; a combination using EXACT is one option. If criteria are spread across separate, non-adjacent ranges, it is often clearer to add separate COUNTIF calculations than to try to combine unrelated cells into one range argument. For more complex logic, helper columns can make the rule visible and easier to audit.

In Google Sheets, formulas are entered directly into a cell or the formula bar in the browser. Excel also has a COUNTIF function with similar basic syntax, but this page uses Google Sheets references and behavior. Sheets menu paths are not needed to use COUNTIF; if you are following instructions written for Excel, be aware that workbook navigation and some interface details differ even when the formula itself is compatible.

Frequently Asked Questions

Can COUNTIF use an entire column as its range?

Yes. A range such as A:A checks the whole column, while A2:A starts below a heading and includes later rows. For a large sheet with many formulas, a bounded range can be more efficient and can make the intended data area easier to understand.

Can COUNTIF count cells that contain a word?

Yes. Use an asterisk before and after the word in the text criterion to match it anywhere in a cell. The match is not case-sensitive. If the word itself includes a wildcard character that should be treated literally, escape that character with a tilde.

Does COUNTIF distinguish uppercase and lowercase?

No. COUNTIF treats text criteria without regard to case, so Apple and APPLE are equivalent for its match. For a case-sensitive count, use a method based on EXACT, such as the SUMPRODUCT example above.

How do I count cells that are not blank?

Use a criterion meaning “not equal to an empty string,” with the range containing the cells to examine. Remember that a formula returning an empty string may appear blank, so check whether your sheet’s formulas and data-entry rules treat those cells as empty for your purpose.

Why does COUNTIF return zero when I can see matching values?

Check that the range includes those cells and that values do not contain leading or trailing spaces. Confirm whether numbers are stored as numbers rather than text, and whether a wildcard is needed for a partial text match. Also check the sheet’s locale if the formula is rejected rather than returning a count.

Make the range and rule explicit

A reliable COUNTIF formula starts with a precise range and a criterion that reflects the values in that range. Use exact text for category counts, operators for numeric or date thresholds, and wildcards only when partial text matches are intended. When a task requires several simultaneous rules, move to COUNTIFS instead of trying to force multiple conditions into COUNTIF. A small check against known rows can catch a wrong range or an unexpected data type before the count becomes part of a report.

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.