Excel Guides

Counting Unique Values in Excel

Excel has a number of ways to count unique values in a range or table. The COUNTIF function can be used to count cells that meet a single criterion, and the COUNTIFS function can be used to count cells that meet multiple criteria. The FREQUENCY function can be used to count how often values occur in a range or table, and the UNIQUE function can be used to return a list of unique values in a range or table.

To count the number of unique values in a range or table, you can use the COUNTIF, COUNTIFS, or FREQUENCY function. For example, suppose you have a list of names in column A, and you want to count how many times each name occurs in the list. You can use the FREQUENCY function to do this:

=FREQUENCY(A2:A10,A2:A10)

This will return an array of values, with each value corresponding to the number of times that value occurs in the list. To get a single value that represents the number of unique values in the list, you can use the SUM function:

=SUM(FREQUENCY(A2:A10,A2:A10))

Note: If your data is not sorted, you may get unexpected results from the FREQUENCY function. In this case, you can use the UNIQUE function instead.


To count the number of unique values in a range or table using the COUNTIF (for a single criterion) or COUNTIFS (for multiple criteria) function, you can use an array formula. For example, suppose you have a list of names in column A and a list of ages in column B, and you want to count how many times each name occurs for each age group. You can use the following array formula:

"=SUM(--(FREQUENCY(IF((A2:A10=B2),ROW(A2:A10)),ROW(A2:A10))>0))" 

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.