Google Sheets COUNTIF for Specific Text

To count cells that contain a particular text value in Google Sheets, use COUNTIF: for example, with status values in B2:B100, =COUNTIF(B2:B100,"Paid") counts cells matching “Paid.” COUNTIF is case-insensitive by default; use wildcards in the criterion when you want to count text that includes a phrase rather than an exact value.

How COUNTIF works

COUNTIF checks a range one cell at a time and counts the cells that meet one condition. Its basic structure is COUNTIF(range, criterion): the range is where Sheets looks, and the criterion describes what qualifies. The function returns a number, so it is useful for summaries, dashboards, survey tallies, inventory lists, and status reports.

For text criteria, put the text in straight double quotation marks inside the formula. The range can be written as a cell reference such as A2:A50 or selected by dragging across the sheet. The function does not change the source data; it recalculates the count as values in the range change.

In Google Sheets in a web browser, enter the formula in an empty cell and press Enter. The formula syntax is also familiar to Excel users, though this article describes Google Sheets. If your spreadsheet’s locale uses semicolons rather than commas between function arguments, use the separator Sheets expects for that file; the function name and criterion logic are the same.

Count an exact text value

For an ordinary text value without wildcard characters, COUNTIF checks for a matching cell value. For example, suppose column B contains order statuses and you want the number marked Paid.

With statuses in B2:B100, this formula returns the number of matching cells; if 18 cells say Paid, the result is 18: =COUNTIF(B2:B100,"Paid")

The comparison is not case-sensitive, so values such as “Paid,” “paid,” and “PAID” are treated as matches. This is often convenient for everyday lists, where capitalization varies unintentionally. COUNTIF does not remove extra spaces, however. A cell containing “Paid ” with a trailing space may not match the intended exact value. If results seem low, inspect the original entries for leading or trailing spaces, inconsistent punctuation, or alternate spellings.

For a criterion stored in another cell, refer to that cell instead of typing the text into the formula. With statuses in B2:B100 and the desired status in D2, use =COUNTIF(B2:B100,D2). This makes the count easy to update: change D2 to another status without editing the formula. Keep the criterion cell free of accidental spaces, since those characters become part of the value being checked.

Count text that contains a phrase

To count cells where the text appears anywhere within a longer value, put an asterisk before and after the phrase. The asterisk is a wildcard meaning “zero or more characters.” For example, it can find a product name inside a longer description or a word inside a note.

With comments in A2:A50, this formula counts cells containing the word apple, including entries such as “green apple” and “apple juice”: =COUNTIF(A2:A50,"*apple*")

Wildcards are useful when entries are not perfectly uniform, but they can also make a result broader than expected. The criterion “*app*” would match many words containing those letters, not just “apple.” Choose a distinctive phrase when possible, and check a few matching rows to confirm the rule is capturing the intended records.

An asterisk at only one end has a different practical effect. With item names in A2:A50, =COUNTIF(A2:A50,"apple*") counts text that starts with apple, such as “apple juice,” but not “green apple.” A criterion of "*apple" counts text that ends with apple. These patterns do not require the whole cell to equal the phrase.

Use question marks and escape wildcards

COUNTIF also recognizes the question mark (?) as a wildcard for one character. This is helpful when one position may vary but the rest of a text pattern is predictable. With codes in A2:A50, =COUNTIF(A2:A50,"?-??") counts entries with one character, a hyphen, and then two characters, such as “A-12.”

If an asterisk or question mark is part of the actual text you need to find, rather than a wildcard, precede it with a tilde (~) in the criterion. With labels in A2:A20, the formula =COUNTIF(A2:A20,"Plan ~*") counts the literal text “Plan *” rather than treating the asterisk as “any sequence of characters.” This distinction matters in data containing symbols, templates, or text copied from another system.

When a criterion includes a wildcard accidentally, the formula may return a number that looks plausible but is too high. If you intend an exact match, use a plain criterion such as “Paid” and check whether the text contains * or ?. Escape those characters when they are meant literally.

Combine COUNTIF with a cell reference

A cell reference is especially useful when someone needs to choose the text to count, or when the same calculation appears in several rows of a report. For example, put a category name in D2 and use the COUNTIF formula in E2, then replace the category in D2 as needed. You can also copy the formula down alongside a list of categories; relative references adjust as the formula moves.

With categories in A2:A100 and the category to count in D2, =COUNTIF(A2:A100,D2) counts exact matches to D2. If D2 contains “Travel,” the result is the number of Travel entries. Notice that the criteria cell supplies the value directly; it does not need quotation marks around the cell reference.

Criteria can also include comparison operators. For example, to count values that are not equal to the text in D2, join the not-equal operator to the cell reference. With categories in A2:A100 and the excluded category in D2, =COUNTIF(A2:A100,"<>"&D2) counts cells whose value differs from D2. This is different from counting a phrase within text: it tests whether the whole cell value meets the comparison condition.

When COUNTIF is case-sensitive

COUNTIF is not the right choice when uppercase and lowercase must count as different values. If “Paid” should count but “paid” should not, use EXACT to make a case-sensitive comparison, and sum the TRUE results. This is an alternative for cases where capitalization carries meaning, such as case-sensitive codes.

With values in B2:B100, the formula =SUMPRODUCT(--EXACT("Paid",B2:B100)) counts cells whose text matches Paid with the same capitalization. The double unary operator converts the TRUE and FALSE results into 1s and 0s, and SUMPRODUCT adds them. For a regular status list where capitalization is not meaningful, the simpler COUNTIF formula is easier to maintain.

Case-sensitive counting and wildcard matching are separate needs. EXACT compares the supplied text to cell contents without treating capitalization as interchangeable; COUNTIF supplies convenient wildcard criteria but ignores case. If a task requires both case sensitivity and a complex pattern, test the formula carefully against representative data before relying on the total.

COUNTIF versus COUNTIFS

COUNTIF handles one condition. If a count depends on more than one condition—for example, a status and a region—use COUNTIFS. It accepts pairs of ranges and criteria, and all the conditions must be satisfied for a row to count. The ranges should have matching dimensions so each row’s values are assessed together.

With statuses in B2:B100 and regions in C2:C100, this formula counts rows where the status is Paid and the region is North: =COUNTIFS(B2:B100,"Paid",C2:C100,"North"). If you only need to count Paid entries regardless of region, COUNTIF is more direct. COUNTIFS is not a way to add multiple alternatives in one criterion; for example, to count two different statuses you can add separate COUNTIF results or design a suitable multi-condition calculation.

COUNTIF and COUNTIFS both support wildcard criteria for text. In a multi-condition count, decide whether each condition should match a whole value or a substring. A broad wildcard in one condition can include records you did not intend, even if the other condition is correct.

Common problems and useful checks

A surprising count is often caused by the data rather than by a broken function. Before changing the formula, confirm that the range includes all relevant rows and excludes headers if the header text could match. Check spelling and spaces in both the source cells and the criterion. A copied value may contain a nonbreaking space or other invisible character that ordinary visual inspection does not reveal.

Check the range: If new rows were added below the selected range, they will not be counted. Extend the range or use a column range when appropriate, such as B:B, while remembering that a whole-column range can make a large sheet do more work.
Check the criterion: Confirm that quotation marks are straight double quotes and that a cell reference points to the intended criterion. Curly quotation marks pasted from formatted text are not valid formula delimiters.
Check for wildcards: An asterisk or question mark in a criterion has special meaning. Escape it with a tilde if it is part of the literal text.
Check the data type: A number stored as text may behave differently from a numeric value. If an imported column mixes types, make the values consistent before interpreting a count.
Check blanks: An empty-string criterion can count empty cells, including cells that appear blank because a formula returns an empty string. With values in A2:A100, =COUNTIF(A2:A100,"") counts entries Sheets treats as empty.

To diagnose a mismatch, temporarily filter or sort the source data and inspect examples that should match but appear not to, as well as examples that seem to be counted unexpectedly. The point is to verify the rule against actual rows, not just to keep changing the formula until the total looks right. For larger workflows, consider cleaning imported text consistently before building summaries.

Frequently Asked Questions

Does COUNTIF in Google Sheets distinguish uppercase and lowercase?
No. COUNTIF treats capitalization as equivalent for ordinary text criteria. Use an EXACT-based formula when the case itself must match.

How do I count cells that contain a word anywhere?
Put an asterisk on both sides of the word in the criterion. For example, with notes in A2:A50, =COUNTIF(A2:A50,"*late*") counts cells containing the letters “late” anywhere in the text. Because this can match larger words too, choose a distinctive search phrase.

Can COUNTIF count two different text values?
Yes. Add separate COUNTIF results when you want either value to count. With statuses in B2:B100, =COUNTIF(B2:B100,"Paid")+COUNTIF(B2:B100,"Pending") returns the combined count of Paid and Pending entries. Use COUNTIFS instead when a row must meet multiple conditions at the same time.

Why does COUNTIF return zero when the text looks identical?
Check for extra spaces, hidden characters, a misspelling, a criterion pointing at the wrong cell, or a range that misses the relevant rows. Also check whether an asterisk or question mark is being treated as a wildcard, or whether the source data has a different type from the value you expect.

Can I use COUNTIF to find text at the start of a cell?
Yes. Put an asterisk after the text to match values that begin with it. For a prefix Apple in A2:A50, =COUNTIF(A2:A50,"Apple*") counts entries beginning with Apple. Use an asterisk before the phrase for an ending match, or on both sides for a phrase anywhere in the cell.

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.