Saturday, February 26, 2011

yogi_Count Comma Separated Values From Cell With Multiple Conditions


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com


Jogi said ...
How can i count comma separated values from a cell with multiple conditions?

My sheet looks like this:

Keywords                    Categories
aeroplane, plane, car       category 1
car, bicycle, plane         category 2
plane                       category 2

Desired output in second sheet:

Keywords    Category 1    Category 2    Sum
car             1             1          2
bicycle         0             1          1
aeroplane       1             0          1
plane           1             2          3


--------------------------------------------------

In my proposed solution, I first computed the unique items and then I computed the number of times the item appears in each category ... so here we go



Jogi commented:


These (formulas in Sheet1) are quite sophisticated formulas :) Frankly, I was looking for a simpler solution, and not so automatic. I don't want to count every single keyword, just some chosen few. So, let's say this is my desired output:


    A        B          C       D
Keywords Category_1 Category_2 Sum
car          1          1       2
bicycle      0          1       1
aeroplane    1          0       1
plane        1          2       3


I am  looking for a formula I can use for each cell separately. For example, in B2: count how many times "car" can be found in column A of Sheet 1 where the side cell from column B = "category 1".
------------------------------------------------------------------


In response to comment from Jogi, I have added Sheet2 in this post ... where formula in cell B2 is ...

=ArrayFormula(sumproduct(iferror(sign(search($A2,'Sheet1'!$A$2:$A))),
('Sheet1'!$B$2:$B=B$1)))