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))"
``````

### Excel

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