Count Each Unique Value in Excel with a Formula

Formulas & functions · Updated November 18, 2026

To count how often each value appears, put your source values in A2:A11, then enter =SORT(UNIQUE(FILTER(A2:A11,A2:A11<>""))) in D2 and =COUNTIF(A2:A11,D2#) in E2. Excel spills a sorted list of nonblank values into column D and the matching counts into column E. This method works in Microsoft 365 and Excel 2021 or later; the steps below also cover PivotTables and older versions.

What You’ll Need

  • A worksheet containing the values you want to summarize. The example uses A1 for a header and A2:A11 for ten entries.
  • Microsoft 365 or Excel 2021 or later for the dynamic formula method. If you have an older version, use the PivotTable or legacy method below.
  • Two empty adjacent columns for the unique-value list and its counts. Keep the cells beneath them clear so spilled results have room.

Step-by-Step

  1. Prepare the source data. Place one item per cell in a single column, with an optional header above it. For example, enter Item in A1 and the values Apple, Banana, Apple, Orange, Banana, Apple, Pear, Orange, Banana, and Apple in A2:A11. Avoid merged cells and blank rows inside the list. A continuous range makes the formulas and PivotTable easier to manage.
  2. Choose where the summary will go. Use D1 and E1 for headers such as Unique value and Count. Check that D2 downward and E2 downward are empty. Dynamic formulas need to spill into those cells; existing content, even a space, can block the result.
  3. Create the unique-value list. With the source values in A2:A11 and the summary headers in D1:E1, enter this formula in D2:
    =SORT(UNIQUE(FILTER(A2:A11,A2:A11<>"")))
    Press Enter. FILTER removes empty cells, UNIQUE returns one instance of each remaining value, and SORT orders the list. In the example, D2:D5 displays Apple, Banana, Orange, and Pear. The functions FILTER, UNIQUE, and SORT are available in Microsoft 365 and Excel 2021 or later, including current Excel for the web. They also work in current Excel for Mac, although ribbon appearance can differ.
  4. Count each item. With the original values still in A2:A11 and the unique list starting in D2, enter this formula in E2:
    =COUNTIF(A2:A11,D2#)
    The # refers to the entire spilled list beginning at D2. Excel returns a count beside each value, spilling the results down column E. For the example, the summary is Apple 4, Banana 3, Orange 2, and Pear 1. The arithmetic checks out: the counts total 10, the number of source entries.
  5. Check what the formula is counting. COUNTIF ignores letter case, so Apple and APPLE are counted as the same value. It does not trim extra spaces: Apple and Apple followed by a space may appear as separate items. Inspect the source if the list looks unexpectedly long, and clean inconsistent entries before trusting the summary.
  6. Include blanks only if you need them. The FILTER part of the first formula deliberately leaves blank cells out. If blank entries matter, you can show their count separately. With source values in A2:A11, enter =COUNTBLANK(A2:A11) in a spare cell. This counts empty cells in that range, including cells that contain formulas returning an empty string. Label the result clearly so it is not mistaken for another text value.
  7. Use a PivotTable as a no-formula option. Select the source, 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 item field to Rows and drag it again to Values. If Values shows Sum instead of Count, open its field menu, choose Value Field Settings, select Count, and choose OK. The row labels show each distinct value, and the Values column shows its frequency. In Excel for Mac, use the Insert tab’s PivotTable command; pane labels may be arranged differently. Right-click inside the PivotTable and choose Refresh after changing the source.
  8. Use a legacy formula if UNIQUE is unavailable. First copy the source values to an unused area or another sheet. Select that copy and use Data > Remove Duplicates to make a distinct-value list; do not run Remove Duplicates on the original data unless you intend to delete entries. With original data in A2:A11 and the distinct labels in D2:D5, enter =COUNTIF($A$2:$A$11,D2) in E2, then fill it down alongside the list. The dollar signs keep the source range fixed as you copy the formula. This approach works in older desktop versions of Excel too.

Common Mistakes

A frequent mistake is using COUNTIF on the source range without first creating or referencing a list of distinct values. COUNTIF returns a count for a criterion; it does not automatically create the summary labels. Another is putting text or a formula below D2 or E2, which prevents a dynamic array from expanding. Leave the spill area empty.

Do not confuse counting each value with counting how many different values exist. The two-column summary reports each value’s frequency. A single total of distinct items is a different result. Also check whether blanks should be excluded: the main formula omits them by design. Finally, remember that COUNTIF is not case-sensitive. If capitalization distinguishes categories in your work, this method will combine them, so you need a case-sensitive approach instead.

For PivotTables, make sure the same field is in both Rows and Values. A numeric field may default to Sum, which adds the numbers rather than counting occurrences. Change the summary calculation to Count. If you edit or add source rows after creating the PivotTable, refresh it; otherwise, the report can show out-of-date totals.

Tips

  • Convert a changing list to a Table. Select a cell in the source and choose Insert > Table, confirming that the table has headers if it does. Tables expand when you add rows and make source data easier to maintain. For a formula based on a Table column, use the column name in place of the fixed range, for example =SORT(UNIQUE(FILTER(Items[Item],Items[Item]<>""))). Here the Table must be named Items and its column must be named Item. The count formula should use that same column as its source range.
  • Keep labels next to counts. A count without its corresponding value is hard to interpret. Use clear headers and avoid sorting just one summary column; sort the complete two-column result together so labels stay with their counts.
  • Verify the total. If blank cells are excluded, add the counts and compare the sum with the number of nonblank source entries. For the example, 4 + 3 + 2 + 1 equals 10. A mismatch can point to blank entries, inconsistent spelling, extra spaces, or a range that does not include every source row.
  • Keep source and summary separate. Put the report in different columns or on another worksheet. That makes it less likely you will overwrite source values and easier to share or print the result.

Troubleshooting

You see #SPILL!: Check the cells below and to the right of the formula for existing values, spaces, or merged cells. Clear the obstructing cells or move the formula to a larger empty area. A dynamic array formula cannot spill inside an Excel Table; place it outside the Table.

You see #NAME? or Excel rejects UNIQUE, FILTER, or SORT: Your Excel version may not include these functions, or the formula may have been entered in an older workbook environment. Use a PivotTable or the Remove Duplicates plus COUNTIF method. In Excel for the web or Mac, check that you are using a current version and that the workbook is editable.

Counts are zero or unexpectedly small: Confirm that the source range in the count formula covers the same entries as the unique-list formula. Look for leading or trailing spaces and spelling variations. If your local Excel settings use semicolons rather than commas as formula separators, use the separator Excel expects; the examples here use commas.

The summary is missing recent entries: Fixed ranges such as A2:A11 do not expand when you type in A12. Update both formulas’ source ranges, or use a Table-based source. For a PivotTable, refresh it and check that its source includes the new rows.

You need to undo a change: If you accidentally removed duplicates from the original list, use Undo immediately if it is still available: Ctrl+Z on Windows or Command+Z on Mac. If you have since made other changes or closed the workbook, restore a saved copy or use version history if available.

Frequently Asked Questions

How do I count the number of different values, not each value’s frequency?
With the values in A2:A11, enter =COUNTA(UNIQUE(FILTER(A2:A11,A2:A11<>""))) in an empty cell. It returns 4 for the example because there are four distinct nonblank values. This formula requires Microsoft 365 or Excel 2021 or later.

Can I count numbers as well as text?
Yes. UNIQUE and COUNTIF can summarize numeric entries, and the same steps apply. Check whether numbers stored as text are mixed with actual numbers, since inconsistent data types can affect how values are grouped or displayed.

Does the formula update when I change the source?
It recalculates when a value in its referenced range changes. But a fixed range such as A2:A11 will not include new entries typed in A12. Expand the range or base the formulas on an Excel Table so added rows are included.

Can I count values separately by capitalization?
Not with this UNIQUE and COUNTIF combination: COUNTIF treats text criteria without regard to case. If Apple and APPLE must be separate categories, use a case-sensitive method, such as a carefully designed SUMPRODUCT and EXACT formula, and test it against your data before relying on the totals.

Can a PivotTable show percentages as well as counts?
Yes. After setting the Values field to Count, add the field again if needed, then open Value Field Settings and use Show Values As to choose a percentage option, such as % of Grand Total. The exact display depends on your Excel version and PivotTable layout.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.