list all unique values and count how many times each appears-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.