Google-sheets – How to count duplicate names as one value in Sheets

filterformulasgoogle sheetsgoogle-sheets-querypivot table

I have a large dataset in Sheets and I'm grouping together the data in a pivot table and have a hard time figuring out how to avoid counting every instance of a name instead of counting the name as one value. Example:

Name      | Genre

ACDC      | rock

ACDC      | rock

Metallica | rock

In the pivot table, I want to see how many rock bands I have for example. When I'm doing the pivot table with the Rows as genres and the values to be the names, instead of showing rock as 2 bands because of AC/DC and Metallica, it's showing 3 because it's counting AC/DC twice.

Best Answer

=TRANSPOSE(QUERY(QUERY({A20:B}, 
 "select Col1,Col2,count(Col1) 
  group by Col1,Col2"), 
 "select count(Col1) 
  where Col2 = 'rock' 
  label count(Col1)'Rock Count:'"))

0

=COUNTA(UNIQUE(FILTER(A20:A, B20:B="rock")))

5

Related Topic