I have an .xls file with a column with some data. How do I count how many unique values contains this column?

I have googled many options, but the formulas they give there always give me errors. For example,

```=INDEX(List, MATCH(MIN(IF(COUNTIF(\$B\$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0))
```

returns

Mar 25, 2022 in Database 922 views

## 1 answer to this question.

Excel may be used to count unique data.
To count unique data in Excel, combine the SUM and COUNTIF functions. = SUM(1/COUNTIF(data, data)=1,1,0) is the syntax for this combination formula. The COUNTIF formula is used to count how many times each value in the range appears.

The resulting array looks like this {1;2;1;1;1;1}:  Divide the obtained values by 1 in the next step. The IF function implements the idea that this step will generate 1 if the values appear just once in the range, otherwise it will generate a fraction value. After that, the SUM function adds up all of the values and returns the result. Because this is an array formula, press Ctrl + Shift + Enter to assign it.
• 23,260 points

## How to count distinct values in Excel

Use functions to count the number of ...READ MORE

## EXCEL How to check if Column A contains specific value and Column B contains 2 specific values?

If you're looking for the string CHECK in cells ...READ MORE

## SQL to find the number of distinct values in a column

In a column, I can choose each ...READ MORE

## What are the ways to get the count of records in a table

With the help of the SQL count ...READ MORE

## MySQL query finding values in a comma separated string

To find MySQL query values in a comma-separated ...READ MORE

## Ordering by the order of values in a SQL IN() clause

Use MySQL's FIELD() function: SELECT name, description, ... FROM ... WHERE id ...READ MORE

## How to rename a column in a database table using SQL?

For SQL Server, use sp_rename USE AdventureWorks; GO EXEC sp_rename 'Customers.CustomerTerritory.TerritoryID', ...READ MORE