CODEWITHSUNDEEP
google-sheets
John N
John N

Reputation: 63

list all unique values and count how many times each appears

A spreadsheet contains multiple rows and columns with names (in varying order), the same name can appear in multiple places, but not necessarily in the same column or row. Looking to list all names and count the number of times each name appears (no duplicates).

Tried the UNIQUE in combination with COUNTIF, but I can't seem to make them work together. :( I'm sure there's some way of nesting formulas to tabulate the results, but I just can't wrap my head around it.

Upvotes: 6

Views: 9577

Answers (2)

Martín
Martín

Reputation: 10084

You can select your whole range in a query like this (change A2:F with your desired range)

=QUERY(FLATTEN(A2:F),"SELECT Col1,COUNT(Col1) where Col1 is not null group by Col1")

enter image description here

Upvotes: 8

Ric
Ric

Reputation: 5722

See this answer on how to stack unique counts when values are in multiple columns. For example if your data is in A1:D10:

=UNIQUE({A1:A10;B1:B10;C1:C10;D1:D10})

Will return a (vertical) list of all unique values. Then use countif in a new column on the whole range (rows, columns) with condition on each of the unique values.

Upvotes: 0

Related Questions