0

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.

0

=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

0

Would the below query serve?

=QUERY(UNIQUE(A20:B),"Select Col2,Count(Col1) where Col1 is not NULL group by Col2",1)

Will just give a list of genres and their respective counts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.