Count unique values in a column in Excel

0 votes

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 enter image description here

Mar 25 in Database by Edureka
• 9,320 points
28 views

1 answer to this question.

0 votes
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.
answered Mar 30 by Edureka
• 8,820 points

Related Questions In Database

0 votes
1 answer

How to count distinct values in Excel

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

answered Mar 15 in Database by Edureka
• 8,820 points
39 views
0 votes
1 answer

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

answered Apr 6 in Database by Edureka
• 8,820 points
28 views
0 votes
1 answer

Find values in a comma separated string in a MySQL query

You can add commas to the left and ...READ MORE

answered Sep 10, 2018 in Database by Sahiti
• 6,360 points
11,237 views
0 votes
2 answers

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

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

answered Aug 20, 2020 in Database by Okugbe
• 280 points
1,840 views
0 votes
1 answer

MySQL query finding values in a comma separated string

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

answered Oct 4, 2019 in Database by Daric
• 500 points
1,461 views
0 votes
1 answer

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

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

answered Feb 4 in Database by Neha
• 6,260 points
212 views
0 votes
1 answer

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

answered Feb 9 in Database by Neha
• 6,260 points
54 views
0 votes
1 answer

SQL count rows in a table

A fast way of doing this would ...READ MORE

answered Feb 21 in Database by Neha
• 6,260 points
73 views
0 votes
1 answer
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP