To count how many times a value appears in Excel, use COUNTIF: if the values are in A2:A20 and the value to check is in D2, enter =COUNTIF(A2:A20,D2). The result is the number of matching cells. You can also use the same function to build a count beside each value, count a specific number or word, or summarize repeated values in a PivotTable.
Before starting, decide whether you want the number of occurrences of one particular value, or a count for every value in the list. The first task needs one COUNTIF formula; the second usually involves copying a formula down or creating a summary. The examples below use Microsoft Excel for Microsoft 365 on Windows. The main formulas also work in recent desktop versions of Excel.
What You’ll Need
- An Excel workbook with the values you want to count, ideally arranged in one column with a header in the row above.
- A spare column or empty cells for the results. Keeping the source data and results separate makes the formulas easier to check.
- The value you want to count, or a plan for making a list of distinct values if you need a count for each one.
You do not need to sort the data first. COUNTIF checks cells wherever they appear in the selected range. If the list is actively changing, use a range that can include new entries, or consider converting the data to an Excel table so the reference can expand as rows are added.
Step-by-Step
- Identify the data range. Find the first and last cells containing the values. For example, a list in A2:A20 has 19 cells to check; A1 might be a column heading and should normally stay outside the range. If your data are in a different column, substitute its actual cell addresses in the examples.
- Choose a cell for the value to look up. Enter the value you want to count in an empty cell, such as D2. For example, type
Redin D2. Using a cell reference makes it easy to count a different value later without editing the formula. - Enter COUNTIF beside the lookup value. With the source values in A2:A20 and the target value in D2, enter
=COUNTIF(A2:A20,D2)in E2 and press Enter. COUNTIF has two required arguments: the range to inspect and the criterion to match. If D2 contains Red and Red occurs four times in A2:A20, E2 returns 4. - Count a typed value directly if you only need one result. With the data in A2:A20, enter
=COUNTIF(A2:A20,"Red")in an empty cell to count cells equal to Red. The quotation marks are required around text typed directly into a formula. A cell reference such as D2 does not need quotation marks around it. - Make the range stay fixed when copying the formula. With the source values in A2:A20 and a list of values to count beginning in D2, enter
=COUNTIF($A$2:$A$20,D2)in E2. The dollar signs lock the source range. Copy E2 down beside the lookup values, such as D3 and D4; Excel changes the criteria reference to D3 and D4 but keeps checking A2:A20. On Windows, drag the fill handle at the lower-right corner of E2 down the column, or select the cells and press Ctrl+D. On Mac, use the fill handle or the equivalent Command+D shortcut where supported by the current Excel setup. - Read the results and check a small example. For instance, if A2:A7 contains Red, Blue, Red, Green, Blue, Red, the count for Red is 3 and the count for Blue is 2. Compare a few results with the visible list before relying on a long summary. A zero means Excel did not find a match under the formula’s matching rules; it does not necessarily mean the value is absent if there are hidden spaces or other data issues.
- Optionally create a list of distinct values first. In Microsoft 365 or Excel 2021, if the source is A2:A20, enter
=UNIQUE(A2:A20)in D2 to return each distinct value once as a spilling list. In E2, with the spilled values beginning in D2 and the source in A2:A20, enter=COUNTIF($A$2:$A$20,D2), then copy it down alongside the distinct values. UNIQUE requires Microsoft 365 or Excel 2021; in older Excel versions, create the distinct list by copying the source column and using Data > Remove Duplicates on a separate copy, or type the categories manually. Do not use Remove Duplicates on the original list if you need to preserve its rows. - Use a PivotTable for a reusable summary. Select a cell in the source data, including its header, then choose Insert > PivotTable. Confirm the table or range and choose where to place the PivotTable. In the PivotTable Fields pane, drag the value field to Rows and drag that same field to Values. Excel commonly summarizes text fields by Count. If the values are numbers and Excel defaults to Sum, open the Values field menu, choose Value Field Settings, select Count, and choose OK. The PivotTable displays each value with its frequency. Refresh it after adding source rows; if the source is an ordinary fixed range, adjust the source range or use a table so new rows are included.
Common Mistakes
Including the header: If the range includes a label such as Product, that label can be counted as a value. Start the range on the first data row unless you intentionally want to include the header.
Using the wrong criterion: COUNTIF matches the value or condition you give it. A formula that points to D2 will count whatever is currently in D2, not necessarily the value you meant to check. Verify both the criterion cell and the source range when a result looks unexpected.
Leaving a copied range unlocked: If you copy =COUNTIF(A2:A20,D2) down, Excel may shift the source range to A3:A21. That can make each row count a different set of cells. Use dollar signs around the source range, as in =COUNTIF($A$2:$A$20,D2), when the same data should be checked for every criterion.
Expecting COUNTIF to distinguish capitalization: COUNTIF is not case-sensitive. It treats Red and RED as matching text. If capitalization is meaningful in your data, COUNTIF alone will not provide a case-sensitive count.
Confusing a blank with a zero: Empty cells and cells containing the number 0 are different. A criterion referring to a blank can count empty cells, while a criterion of 0 counts cells with zero values. Check whether the cells are genuinely empty or contain formulas that return an empty string before interpreting the result.
Typing text criteria without quotation marks: A typed word inside a formula needs straight double quotation marks, as in =COUNTIF(A2:A20,"Red"). If the criterion is a cell reference, use the reference without quotes, as in =COUNTIF(A2:A20,D2).
Tips
For a numeric condition rather than an exact value, combine the comparison operator with a criterion in quotation marks. For example, with scores in B2:B20, enter =COUNTIF(B2:B20,">=80") in an empty cell to count scores of 80 or higher. The result is the number of cells meeting that threshold. If the threshold itself is in D2, use =COUNTIF(B2:B20,">="&D2); the ampersand joins the operator text to the cell value.
COUNTIF also supports wildcard characters for text criteria. An asterisk represents any sequence of characters and a question mark represents one character. With names in A2:A20, =COUNTIF(A2:A20,"Ann*") counts entries that begin with Ann, such as Ann and Anne. A tilde can escape a wildcard when you need to search for a literal asterisk or question mark. Use wildcards only when a partial match is intended; they can otherwise count more cells than expected.
If you will keep adding data, format the source range as an Excel table with Insert > Table. Tables expand as you add rows, and formulas that refer to table columns are easier to read than fixed cell addresses. Check that the formula or PivotTable source actually includes the new rows, though: an existing ordinary range does not automatically grow just because data were added below it.
For a quick visual check, select the data column and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated entries but does not produce a numeric count; use COUNTIF or a PivotTable when you need totals. It can still help reveal extra spaces, inconsistent spelling, or values that look similar but are not identical.
Keep your formula results outside the source range. If the count formula is placed inside the range it counts, the formula may include its own cell or create confusing references. Also remember that sorting the source list is optional: a count formula finds matches in the whole specified range regardless of order.
Troubleshooting
The result is zero, but the value appears in the list. Look for leading or trailing spaces, nonbreaking spaces copied from websites, spelling differences, and numbers stored as text. Click a source cell and inspect the formula bar; compare it with the criterion cell. A quick remedy for ordinary extra spaces is to create a cleaned helper column with TRIM, then count that cleaned column. TRIM does not remove every kind of invisible character, so pasted web data may need further cleaning.
The result is too high. Check whether the criterion contains a wildcard such as * or ?, or whether a condition is intentionally matching more than one exact value. Confirm that the selected range excludes unrelated rows and headings. Also remember that COUNTIF does not distinguish upper- and lowercase text.
The formula appears as text instead of returning a number. Change the cell format from Text to General, then edit the cell and press Enter again. Check that the formula begins with an equals sign and that there is no apostrophe or leading space before it. If Excel shows formulas throughout the sheet, toggle Show Formulas from the Formulas tab or press Ctrl+` on Windows; the Mac shortcut can differ by keyboard layout.
Excel shows an error after you enter the formula. Confirm that the range and cell references are valid and that the parentheses are balanced. In Excel for Microsoft 365 on Windows, formulas normally use commas between arguments. Some regional settings use semicolons instead; if Excel rejects the comma version, use the list separator configured for your system. A text criterion typed directly into the formula still needs straight quotation marks.
The PivotTable count is missing or shows a sum. Open Value Field Settings for the field in the Values area and choose Count. If the PivotTable does not include recent records, refresh it and check its data source. Excel for the web has PivotTable features too, although some creation and management options may differ from desktop Excel; use the desktop app if the needed field settings are not available.
Frequently Asked Questions
How do I count how many times each value appears?
Make a list of the values you want to summarize, then put a COUNTIF formula beside the first one and copy it down. Lock the source range with dollar signs so every formula checks the same data. A PivotTable is a good alternative when you want Excel to produce the categories and counts together.
Can I count duplicates without counting the first occurrence?
COUNTIF gives the total number of occurrences, including the first. To find values that appear more than once, count each candidate and treat results greater than 1 as duplicates. Conditional Formatting can highlight duplicates visually, but it does not subtract the first occurrence or provide a separate duplicate-only total.
Does COUNTIF count numbers and text?
Yes. It can count text, numbers, dates, and criteria such as greater than or equal to a threshold. Make sure numbers that should be numeric have not been stored as text, since mixed data types can make comparisons behave differently than expected.
Can I count a value across more than one column?
Yes. For a rectangular range, use that range as the first COUNTIF argument; for example, a range covering A2:C20 will check all its cells. If the columns are separate, nonadjacent ranges, use a separate COUNTIF for each range and add the results, or arrange the data in one continuous range for a simpler formula.
Is COUNTIF case-sensitive?
No. COUNTIF treats upper- and lowercase versions of the same text as matches. It is suitable for ordinary frequency counts, where capitalization is usually not meaningful, but a case-sensitive requirement needs a different formula approach.
Will the count update when I edit the data?
Yes, a COUNTIF formula recalculates when a value in its referenced range or criterion cell changes. If you add rows outside a fixed range, however, those new rows will not be counted until you expand the reference or use a source table. A PivotTable may also need to be refreshed to show changed or newly added records.
Related
- Copy in Excel and Paste as an Image: Step-by-Step
- How to Copy in Excel and Keep Formatting
- How to Copy an Image in Excel: Clear Steps and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculate Age in Months in Excel: Formulas and TipsTo calculate completed age in months in Excel, put a birth date in A2 and enter =DATEDIF(A2,TODAY(),"m") in…
- VLOOKUP Between Two Sheets: Examples and Common FixesTo use VLOOKUP across two Excel sheets, put the lookup value on one sheet and the data table…
- How to Filter Information in Excel: A Practical GuideTo filter information in Excel, select a cell in your data, choose Data > Filter, then use the…
- Excel Filter Table Without Hiding Rows: Best OptionsExcel’s built-in filters hide rows that do not match, so you cannot use a standard table filter to…
- Conditional Formatting Formula If Statement in ExcelIn Excel, a conditional-formatting formula must evaluate to TRUE or FALSE for the cell or row you want…
- How to Add Filter Options in Excel: A Step-by-Step GuideTo add filter options in Excel for Microsoft 365 on Windows, select a cell in your data, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.