To find how many times a value appears in an Excel range, use =COUNTIF(A2:A10,D2), where A2:A10 contains the values and D2 contains the value to count. COUNTIF counts matching cells, without regard to capitalization; for example, if “Red” appears five times in the range, the result is 5.
How COUNTIF counts values
COUNTIF is the standard function for counting cells that meet one condition. Its syntax is =COUNTIF(range,criteria): the range is where Excel looks, and the criteria describes what should count. The result is a number, so you can use it in other formulas, charts, or summaries.
For example, suppose A2:A10 contains Red, Blue, red, Green, Red, a blank, Red, Blue, and Red. With the word Red in D2, the formula =COUNTIF(A2:A10,D2) returns 5. Excel treats Red and red as the same match. The blank cell is not counted because the criterion is Red.
COUNTIF counts matching cells, not every appearance of a word inside a cell. If one cell contains “Red, Red, Blue,” it counts as one matching cell when the criterion is Red. To count repeated text within a cell, use a different approach described below.
Enter the formula in a worksheet
For a quick count, choose an empty cell outside the data, type the formula, and press Enter. Use a cell reference for the value to count if you want to change the target without editing the formula.
- Identify the cells containing the list, such as A2:A100.
- Enter the value you want to count in another cell, such as D2.
- In an empty cell, enter
=COUNTIF(A2:A100,D2), replacing the example addresses with your own. - Press Enter. Change D2 to count a different value in the same range.
In Microsoft 365 for Windows, formulas use commas between arguments under the usual regional settings. Some regional settings use semicolons instead. If Excel rejects a formula with commas, check the list-separator setting used by your copy of Excel. The COUNTIF function and its argument order are the same.
Count a typed value or use a cell reference
You can put a text value directly in the criteria argument, enclosed in straight double quotation marks. With colors in A2:A20, the formula =COUNTIF(A2:A20,"Blue") counts cells matching Blue. A reference such as D2 is usually easier to maintain: someone can enter another target in D2 without changing the formula.
For a numeric target, use a number or a cell reference. With numbers in A2:A20, the formula =COUNTIF(A2:A20,25) counts cells equal to 25. If you want to count numbers greater than a target stored in D2, combine the comparison operator with that reference: =COUNTIF(A2:A20,">"&D2). The ampersand joins the greater-than operator to the value in D2; the result is the number of cells greater than that value.
Comparison criteria can also use less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>). Put a text criterion that contains an operator in quotation marks. For a date comparison, use a date stored in a cell as the reference rather than typing a date in a format that might be interpreted differently under another regional setting.
Find partial text with wildcards
COUNTIF supports wildcards when the criterion is text. An asterisk (*) stands for any number of characters, including none; a question mark (?) stands for exactly one character. These are useful when you want to count names, codes, or descriptions that share a pattern.
=COUNTIF(A2:A20,"North*")counts cells whose text begins with North.=COUNTIF(A2:A20,"*east*")counts cells containing east anywhere in the text.=COUNTIF(A2:A20,"B?7")counts three-character entries beginning with B and ending with 7, with any one character between them.
For a concrete example, if A2:A7 contains Northeast, East, Southeast, West, east-side, and a blank, =COUNTIF(A2:A7,"*east*") returns 4. It matches Northeast, East, Southeast, and east-side. The search is not case-sensitive, so east-side matches even though the pattern is lowercase.
If the text you want to find includes a literal asterisk or question mark, precede that character with a tilde (~) in the criterion. For instance, the criterion "~*" asks Excel to match an actual asterisk rather than treat it as a wildcard. A tilde used as a literal character is escaped with another tilde.
Count repeated text inside one cell
When the question means “how many times does this text occur within a cell?” rather than “how many cells contain this value?”, COUNTIF alone is not enough. One option compares the length of the original text with the length after removing the target text, then divides the difference by the target’s length.
With the text in A2 and the word cat as the target, enter =(LEN(LOWER(A2))-LEN(SUBSTITUTE(LOWER(A2),"cat","")))/LEN("cat"). If A2 contains “Cat catapult CAT,” this formula returns 3. LOWER makes the comparison case-insensitive, and SUBSTITUTE removes each non-overlapping occurrence before the length difference is calculated. This counts cat even when it is part of a longer word, as in catapult.
That method counts occurrences within one cell. It also has limitations: SUBSTITUTE is case-sensitive by itself, which is why LOWER appears in the example, and the formula counts non-overlapping matches. If your goal is to count whole words only, rather than letter sequences embedded in other words, the formula needs additional boundary handling.
Count with more than one condition
Use COUNTIFS when a row must meet multiple conditions. Unlike COUNTIF, it accepts pairs of ranges and criteria. Each range must be the same size as the others so Excel can test corresponding cells on the same row.
With departments in A2:A20 and order statuses in B2:B20, enter =COUNTIFS(A2:A20,"Sales",B2:B20,"Closed") to count rows where the department is Sales and the status is Closed. If three rows meet both conditions, the result is 3. The criteria are joined with AND logic: a row counts only when it satisfies both.
Criteria can also refer to cells. With the same layout, and the desired department in D2 and status in E2, use =COUNTIFS(A2:A20,D2,B2:B20,E2). This is convenient for a small summary table: list categories and statuses in cells, then fill the formula down or across while keeping the data ranges fixed if needed.
Case sensitivity, blanks, and hidden rows
COUNTIF does not distinguish uppercase from lowercase. If A2:A10 contains the example list above and D2 contains Red, =COUNTIF(A2:A10,D2) returns 5, including the lowercase red. For a case-sensitive count, with the same range and target cell, use =SUMPRODUCT(--EXACT(A2:A10,D2)). It returns 3 because exactly three cells contain Red with that capitalization.
To count blank cells, use COUNTBLANK. With one genuinely empty cell in A2:A10 and no formulas returning empty text, =COUNTBLANK(A2:A10) returns 1. COUNTIF can also use an empty-string criterion, but be aware that a cell containing a formula that returns an empty string can behave like a blank for this kind of count. COUNTA is different: it counts cells containing formulas even when those formulas display no text.
COUNTIF counts matching cells even if rows are hidden or excluded by a filter. If you need a count of only the visible filtered records, a plain COUNTIF may give a larger result than expected. Filter the list and inspect the visible records, or build a summary designed to account for the filter; do not assume COUNTIF automatically ignores hidden rows.
Common counting problems and checks
If the result is zero or unexpectedly high, check the range, criterion, and underlying cell contents before changing the formula. A value that looks identical on screen may contain a leading or trailing space, a different punctuation mark, or a nonprinting character. Trimming or cleaning imported data can help, but keep an untouched copy if the original text matters.
- Check the selected range. Confirm that it includes all intended data rows and excludes headers if appropriate. A formula copied down may shift its range; use dollar signs, as in
=COUNTIF($A$2:$A$100,D2), to keep the list range fixed. - Check the criterion. Make sure a text criterion is spelled correctly and that an operator such as > is joined to a cell reference when required.
- Check data types. Numbers stored as text can behave differently from numeric values in comparisons. If imported figures are meant to be numbers, inspect and convert them consistently rather than relying on how they look.
- Check wildcard behavior. A criterion containing * or ? is a pattern, not necessarily a literal search. Escape literal wildcard characters with ~.
- Check hidden rows and duplicates. COUNTIF counts every matching cell in its range, including repeated entries and filtered-out rows.
For a more readable or repeatable analysis, consider converting the list to an Excel Table with Insert > Table. Tables expand as rows are added, and structured references can make formulas easier to understand. You can still use COUNTIF with a table column; the important point is to ensure the formula refers to the intended field.
COUNTIF compared with other ways to count
COUNTIF is best when you need a reusable formula for one condition. COUNTIFS is the natural choice for two or more conditions. For a one-time check, you can use the column filter to display a category and inspect the visible rows, but filtering changes what is displayed rather than creating a standalone count formula.
For a recurring summary of many categories, a PivotTable may be more useful than writing a separate COUNTIF for every value. Select the data and choose Insert > PivotTable. Add the category field to Rows and add it to Values; if necessary, change the Values field setting to Count. A PivotTable can summarize counts across categories and can be refreshed when the source data changes. COUNTIF is usually quicker for a small, fixed calculation, while a PivotTable is useful when readers need to explore several groupings.
The status bar can also show a quick count of selected nonempty cells, depending on the selected data and enabled status-bar calculations. That is not the same as counting one particular value: selecting a column and reading Count will not tell you how many cells equal Red. Use COUNTIF when the matching criterion matters.
Frequently Asked Questions
How do I count how many times a number appears in Excel?
With numbers in A2:A100 and the target number in D2, enter =COUNTIF(A2:A100,D2). The result is the number of cells in that range equal to the value in D2.
Does COUNTIF count uppercase and lowercase as different values?
No. COUNTIF is case-insensitive, so Red and red match the same criterion. For a case-sensitive comparison, use SUMPRODUCT with EXACT.
How do I count cells that contain a word anywhere in the text?
Use asterisks around the word as wildcards. With text in A2:A100, =COUNTIF(A2:A100,"*east*") counts cells containing east anywhere in their text, regardless of capitalization.
How can I count a value only when another column also matches?
Use COUNTIFS. For example, with departments in A2:A100 and statuses in B2:B100, =COUNTIFS(A2:A100,"Sales",B2:B100,"Closed") counts rows meeting both conditions.
Why does my count include rows I filtered out?
COUNTIF evaluates the entire specified range, including rows hidden by a filter. A filter controls which rows are shown; it does not automatically change COUNTIF’s range or make the function count visible rows only.
Related
- Excel to PowerPoint Linked Cells: How to Link and Update
- Convert Excel to PowerPoint: Methods, Steps, and Tips
- Convert PowerPoint to Excel: Steps, Options, and Limits
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Can You Make a Word Document a Fillable Form in Word?Yes. In Microsoft Word for Microsoft 365 on Windows, add content controls from the Developer tab—such as text…
- How to Archive Outlook Emails to Your C Drive as a PSTTo archive Outlook emails on your C drive, use classic Outlook for Windows to export the mailbox or…
- Microsoft Excel Insert Page Break: A Practical GuideTo insert a page break in Microsoft Excel for Microsoft 365 on Windows, select the cell below and…
- Present PowerPoint in Teams With Notes: How It WorksYes. Microsoft Teams can present a PowerPoint while you privately view your speaker notes by using PowerPoint Live:…
- Excel Data Validation Dependent Drop-Downs: Setup GuideTo create a dependent drop-down in Excel for Microsoft 365 on Windows, make a named range for each…
- Corrupted Excel File Repair: What to Try FirstFor a corrupted Excel file, first make a copy of the file, then try Excel’s built-in Open and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.