1

How do I list and count unique comma-separated values (column B in the example below) if the number in column A is larger (or smaller) than X? In other words, how do I turn the below table...

   Day   |          Fruits
+--------|--------------------------+
         |
    20   |   Apple, Banana, Pearl
         |
    24   |   Apple, Pearl
         |
    32   |   Banana, Pearl
         +

...into this , with criteria: Day < 28.

  Fruit    |   Frequency
+----------|---------------+
           |
  Apple    |       2
           |
  Pearl    |       2
           |
  Banana   |       1
           +

A solution proposed by @AdamL in this question is really close to what I want to achieve, but I can't figure out how to list values based on criteria from another column. Here's what Adam came up with:

=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN(",",A:A),",")&{"";""}),"select Col1, count(Col2) group by Col1 label count(Col2) ''",0))
Adam
  • 93
  • 5

3 Answers3

2

use:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(
 IF(IFERROR(SPLIT(B2:B, ","))="",,A2:A&"♦"&TRIM(SPLIT(B2:B, ",")))), "♦"),
 "select Col2,count(Col2) 
  where Col1 < 28 
    and Col1 is not null 
  group by Col2 
  label count(Col2)''"))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
1

I think it's a bit easier to filter on column A first then split:

=ArrayFormula(query(flatten(split(filter(B2:B,A2:A<28,A2:A<>""),",")),"select Col1,count(Col1) where Col1 is not null group by Col1 label Col1 'Fruit',Count(Col1) 'Count'"))

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
0

Considering Column 'A' as days and Column 'B' as fruits with row 1 as headers, AdamL's formula works for you with a little tweak as below:

=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN(",",B2:B),", ",True)&{"";""}),"select Col1, count(Col2) group by Col1 label count(Col2) ''",0))

Hope this solves your problem.

Harsh
  • 209
  • 4
  • 12