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)))
No comments:
Post a Comment