CODEWITHSUNDEEP
google-sheetsspreadsheet
Isky
Isky

Reputation: 1398

Count the repetition of text in a list using google spreadsheet

I've got a list of text suppose from A1 to A{n} like these:

Alternative
Ambient
Rock
Metal
ecc..

Then I've got a list of text in each cell divided by ";"
So for example in the first cell I've got

Alternative; Rock; Pop

I've got a list like these for {n} columns, suppose from C1 to C{n}.
What I need to do is COUNT how many times each text (from A1 to A{n}) is present in each list for each column.

So for example suppose I've got two columns:

Alternative; Rock; Pop
Ambient; Rock; Metal

What I need to know is that Alternative is present only once, Rock two times and so on and get it from B1 to B{n}.

So what I expect is something like

A                 B
Alternative       1
Rock              2

A text cannot be repeated twice in the same cell.

I've tried using COUNTIF(SPLIT()) but I came up with no result.

Thanks.

Upvotes: 0

Views: 184

Answers (1)

Zak
Zak

Reputation: 1100

You're close, but you'll need to also use JOIN to put your columns together before splitting them, TRIM to remove leading and trailing whitespace (so that matching is easier), and ARRAYFORMULA to apply TRIM to all the results of SPLIT (as TRIM only works on on a single value.)

Putting it all together, you'd have something like the following in B1:

=COUNTIF(ARRAYFORMULA(TRIM(SPLIT(JOIN(";", $C$1:$C$2), ";"))), A1)

Upvotes: 2

Related Questions