CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Алексей Наумов
Алексей Наумов

Reputation: 189

Calculate the number of elements in each category in Google Sheets

I have a table in Google spreadsheet. It has two columns: students' names and their hobbies. I need to create a separate table (page?) that will calculate the number of students doing each hobby.

Example can be found >> here <<

An additional problem is that the number of categories is unknown it would be nice to also have a list of these.

The supposed result is shown in italics in the table above.

Upvotes: 2

Views: 6176

Answers (2)

Andy
Andy

Reputation: 13

Updated example sheet: https://docs.google.com/spreadsheets/d/1HDBTjxO3w9YHeYWUqhq2FPnA1U1VUwTtoN9zeZYXAK8/edit?usp=sharing

To select the list of categories using:

=SORT(UNIQUE(B2:B))

Will select each unique valve from the column B starting from row 2 through to the end.

For counting the number of students doing each hobby the below:

=ARRAYFORMULA(IF(D2:D="","",COUNTIF(B2:B,D2:D)))

COUNTIF will count the number of occurrences in column B that relate to the data in column D.

We use ARRAYFORMULA in this case to do the above COUNTIF for each row that is not blank determined by the IF statement. IF column D is blank (" ") then the output will be blank (" ") however if it's not, the COUNTIF will run.

Summary: Table on the right will update additional hobbies as you add then to the list on the left then count the number found.

Upvotes: -1

player0
player0

Reputation: 1

try:

=QUERY(Sheet1!A2:B, "select B,count(B) where A !='' group by B label count(B)''", 0)

0

Upvotes: 4

Related Questions