3

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.

Ken White
  • 123,280
  • 14
  • 225
  • 444
John N
  • 33
  • 3
  • can you post the examples/attempts that you have tried so far? – kyrlon Dec 20 '22 at 00:10
  • At this spreadsheet: https://docs.google.com/spreadsheets/d/1r_3NjtKnqniRjP4iS5H9D27zFxHlImLkCvTpy52hOgk/edit?usp=sharing I can use unique(E2:P9) to extract unique values, but it displayed then all in column/row format (almost duplicating what I have already) – John N Dec 20 '22 at 00:21

2 Answers2

3

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

Martín
  • 7,849
  • 2
  • 3
  • 13
  • OK, that works, I just clearly have no idea why or how... thank you? Thank you! – John N Dec 20 '22 at 00:26
  • FLATTEN stacks a whole range into one column, then with QUERY I ask to count how many are there in that single column – Martín Dec 20 '22 at 00:27
  • *mind blown THANK YOU – John N Dec 20 '22 at 00:28
  • Here if you want to read about [FLATTEN](https://support.google.com/docs/answer/10307761?hl=en) and [QUERY](https://support.google.com/docs/answer/3093343?hl=en) - Good luck! – Martín Dec 20 '22 at 00:30
0

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.

Ric
  • 5,362
  • 1
  • 10
  • 23