COUNTIF in Google Sheets: Syntax, Examples, and Tips

In Google Sheets, COUNTIF counts cells that meet one condition. If A2:A6 contains Maya, Leo, Maya, Noor, and Maya, enter =COUNTIF(A2:A6,"Maya") to get 3. The function is useful for counting matching names, values above a threshold, dates in a period, or text that contains a particular word.

How COUNTIF works

COUNTIF checks each cell in a range against a criterion and returns the number of cells that match. It counts cells; it does not add their values or return the matching rows. Its two arguments are the range to examine and the condition to apply. The condition can be a value, a text string, a comparison such as “greater than 10,” or a reference to another cell.

COUNTIF applies one condition to one range. For example, it can count orders marked “Complete,” or scores at least 85. It cannot directly count rows only when two separate conditions are both true. For that, use COUNTIFS, covered below. COUNTIF is not case-sensitive: a criterion for Maya also matches MAYA or maya. An exact text criterion still matches the whole cell, however; it does not match a longer value that merely contains the word.

Count exact text or values

For a basic name count, assume A2:A6 contains Maya, Leo, Maya, Noor, and Maya. The formula =COUNTIF(A2:A6,"Maya") returns 3. Text criteria go in straight double quotation marks. If the criterion is a number, it can be supplied as a number without quotation marks.

For example, suppose B2:B9 contains the scores 72, 88, 91, 64, 88, 76, 95, and 88. To count scores of at least 85, use =COUNTIF(B2:B9,">=85"). It returns 5: the matching scores are 88, 91, 88, 95, and 88. Comparison symbols such as >, <, >=, and <= are part of the criterion, so the operator and number are written together inside quotation marks.

A criterion can also come from a cell, which is helpful when you want to change the value being counted without editing the formula. With the same names in A2:A6 and the text Maya in D1, =COUNTIF(A2:A6,D1) returns 3. Because D1 supplies the criterion, it is a cell reference rather than quoted text.

Use comparison criteria

Comparison criteria let you count values that meet a threshold, such as quantities below a reorder point or scores in a particular range. The comparison operator belongs in the criterion. For example, a criterion of “<10” counts values below 10, while “<>0” counts values not equal to zero. If a comparison should use a value stored in another cell, the operator must be joined to that reference; a plain reference by itself means “match this value,” not “compare against this value.”

Check the underlying data type when a comparison gives a surprising result. A number stored as text may not behave like a numeric value, and text that looks like a date may not be a real date value. Google Sheets may display a number as currency or a date because of formatting, but the displayed appearance is not the same thing as the value stored in the cell. If counts look wrong, inspect a few source cells and confirm they contain the intended type of data.

Count text that contains a word

COUNTIF supports two wildcard characters for text criteria. An asterisk (*) stands for any number of characters, including none. A question mark (?) stands for exactly one character. For instance, suppose A2:A8 contains North, Northeast, South, North Hub, north, West, and a blank cell. The formula =COUNTIF(A2:A8,"North*") returns 4: North, Northeast, North Hub, and north all begin with those letters. The match is case-insensitive.

Wildcards are useful for counting entries with a shared prefix, suffix, or phrase. A criterion such as “*road*” can match text with road anywhere in the cell, while “?at” matches three-character entries ending in at. These characters are wildcards, not regular expressions. If you need to search for a literal asterisk or question mark in the cell text, place a tilde (~) immediately before the character in the criterion. This tells COUNTIF to treat it as a character rather than a wildcard.

Keep in mind that a wildcard criterion searches text patterns. It is not a substitute for a numeric comparison. If the goal is to count values over a threshold, use a comparison criterion such as “>=85,” rather than trying to describe the numbers with text wildcards.

Count dates in Google Sheets

Dates in Sheets are stored as numeric date values, so COUNTIF can compare them with the same operators used for numbers. Suppose C2:C6 contains the actual dates December 31, 2025; January 1, 2026; January 2, 2026; February 10, 2026; and March 1, 2026. To count dates after January 1, 2026, enter =COUNTIF(C2:C6,">"&DATE(2026,1,1)). It returns 3: January 2, February 10, and March 1 are later than the cutoff; January 1 itself is not.

Using DATE to create the comparison date avoids relying on how a typed date string is interpreted under different spreadsheet locales. The example uses a strict greater-than sign. To include the cutoff date, the condition would need to be greater than or equal to it. Make sure the source cells contain date values rather than text that only looks like a date; consistent date formatting can make a sheet easier to inspect, but formatting alone does not convert text into a date.

When to use COUNTIFS instead

Use COUNTIF when one condition is enough. Use COUNTIFS when every counted row must meet multiple conditions, such as a particular person and a particular status. Each condition in COUNTIFS is paired with its own range, and the ranges should cover corresponding rows.

For example, assume A2:A6 contains Maya, Maya, Leo, Maya, and Noor, while B2:B6 contains Complete, Pending, Complete, Complete, and Complete. To count rows where the name is Maya and the status is Complete, enter =COUNTIFS(A2:A6,"Maya",B2:B6,"Complete"). It returns 2. The first Maya row and the third Maya row meet both conditions; the row marked Pending does not. COUNTIFS uses AND logic between its criteria pairs: a row must satisfy each supplied condition to be counted.

If the question is whether a row meets either of two alternatives, a single COUNTIFS formula may not express that OR logic. One straightforward approach is to count each condition separately and add the results, taking care not to count overlapping matches twice. The right approach depends on whether the alternatives can overlap and on the shape of the data.

Common COUNTIF problems and fixes

Missing quotation marks: Text criteria and comparison criteria are normally written as quoted strings, such as “Complete” or “>=85”. A cell reference used as the criterion, by contrast, is not enclosed in quotation marks.
Using the wrong match type: A criterion for Maya matches the complete cell text, not a longer entry such as Maya Chen. Use a wildcard when a partial-text match is intended.
Dates stored as text: A value that looks like a date may not compare as a date. Check whether the source cells contain real date values before changing the formula.
Unexpected blanks: A blank-looking cell may actually contain a formula that returns an empty string, spaces, or other invisible characters. Those are not always equivalent to a genuinely empty cell. Inspect the source data if a blank-related count seems off.
Wrong range: Confirm that the range includes all intended rows and does not include headers unless you want headers considered. For COUNTIFS, make sure its paired ranges align row by row.
Locale differences: Google Sheets uses spreadsheet locale settings that can affect formula separators and how dates or numbers are interpreted. If a copied formula is rejected, check the locale and the separator style used by the sheet.

When troubleshooting, test the criterion against a few known cells first. Check for extra spaces, inconsistent spelling, and values stored in different formats. In collaborative sheets, a dropdown or consistent data-entry rule can reduce variations such as “Complete,” “Completed,” and “complete ” that make a count harder to interpret.

Practical range and formula choices

A bounded range such as A2:A500 makes it clear which rows the formula covers and can be easier to maintain in a structured sheet. A whole-column range is convenient when new rows are added frequently, but it asks Sheets to consider a much larger range. Choose a range that fits how the data grows, and check it after inserting or moving rows.

For repeated reporting, put a changing criterion in a separate cell and refer to it from the formula. This makes the count easier for another person to adjust and reduces the chance of mistyping a criterion in several places. If you need to count several categories, you can place each category label in a summary table and use a separate COUNTIF beside each label. If the task needs several conditions per row, use COUNTIFS rather than making a series of increasingly complicated single-condition formulas.

Google Sheets and Excel both provide COUNTIF with the same basic purpose and familiar argument structure. Formulas using common criteria usually transfer between them, but locale settings, date interpretation, and some spreadsheet features can differ. When sharing a file between programs, check that the result still matches a few known records rather than assuming every detail of the workbook will behave identically.

Frequently Asked Questions

Is COUNTIF case-sensitive in Google Sheets?
No. COUNTIF treats uppercase and lowercase text as equivalent, so a criterion of Maya matches Maya, MAYA, and maya. If case-sensitive matching is essential, COUNTIF alone is not the right tool.

How do I count cells that contain a word anywhere?
Use an asterisk on both sides of the word in the criterion. For example, with text in A2:A20, a criterion pattern like *road* counts entries containing road at any position. Remember that the asterisks are wildcards, so the pattern can also match longer words containing those letters.

Can COUNTIF count more than one condition?
COUNTIF applies one condition. For two or more conditions that must all be true, use COUNTIFS and provide a range and criterion for each condition. If you need OR logic, plan for possible overlap before adding separate counts.

Why does COUNTIF return zero when the value appears?
Check for extra spaces, a different data type, a misspelled criterion, or a partial-versus-exact matching issue. Also confirm the value is inside the range being counted. A cell can look like a number or date while actually containing text.

Can COUNTIF count blank cells?
It can be used with a blank criterion, but blank-looking cells may include formulas returning empty text or invisible spaces. Those cases can behave differently from truly empty cells, so inspect the data and test the result against a few cells before relying on a blank count.

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.