Menu
Is there a simple way to retrieve a list of all unique values in a column, along with how many times that value appeared?
Example dataset:
A
A
A
B
B
C
... Would return:
A | 3
B | 2
C | 1
287
You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM(IF(1/COUNTIF(data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears. The resulting array looks like {1;2;1;1;1;1}.
In Excel, there are several ways to filter for unique values—or remove duplicate values: To filter for unique values, click Data > Sort & Filter > Advanced. To remove duplicate values, click Data > Data Tools > Remove Duplicates.
Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows. SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName FROM yourTableName; To understand the above syntax, let us create a table. Display all records from the table using select statement.
Use GROUP BY:
select value, count(*) from table group by value
Use HAVING to further reduce the results, e.g. only values that occur more than 3 times:
select value, count(*) from table group by value having count(*) > 3
SELECT id,COUNT(*) FROM file GROUP BY id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
