CODEWITHSUNDEEP
google-sheets
Tangy
Tangy

Reputation: 13

Getting the count of a column from a set of comma separated values?

I have two columns and I want to get a list of unique Places with the a sum of the Count from the next column.

For example:

Current

Places (ColumnA)                  Count(ColumnB)
Italy, Greece, France             10
Italy, Greece                     5
France                            1

Desired outcome

Places (ColumnD)                  Count(ColumnE)
Italy                             15
Greece                            15
France                            11

Upvotes: 1

Views: 132

Answers (1)

JPV
JPV

Reputation: 27262

Assuming your data starts in row 2, try in D2:

=ArrayFormula({unique(trim(transpose(split(concatenate(A2:A&","),",")))),sumif(A2:A, "=*"&unique(trim(transpose(split(concatenate(A2:A&","),","))))&"*",B2:B)})

or, alternatively:

=query(ArrayFormula({transpose(split(query(substitute(A2:A,",",""),,50000)," ")),transpose(split(concatenate(rept(B2:B&char(9), len(A2:A)-len(substitute(A2:A, ",",""))+1 )),char(9)))}), "select Col1, sum(Col2) where Col1 <>'' group by Col1 label sum(Col2)'' ")

Yet another way (credits to AdamL for this one):

=ArrayFormula(QUERY(TRANSPOSE(SPLIT(QUERY(REPT(A2:A&", ",B2:B),,ROWS(A2:A)),", "))&{"",""},"select Col1, count(Col2) group by Col1 label count(Col2) ''",0))

Upvotes: 3

Related Questions