• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count/Sumproduct for multiple values within a cell

3G

3G

Member
Greetings-


Similar question I asked here : http://chandoo.org/forums/topic/sumproduct-question-2


I've got a list of 7 items (A2:A8). On another sheet, I have cells that have multiple combinations of these 7 items. I basically need to count the number of occurences of each item. Data looks like this:


List 1:

A|

Frog

Sheep

Dog

Chicken


Sheet 2:

Frog, Sheep

Dog, Chicken

Dog,Sheep, Frog

Dog, Chicken, Sheep


So, if I were counting sheep (hehe) I'd get the result of "3".


I've messed around with SUMPRODUCT a bit, and tried changing the formulas provided in the other thread above, but, I can't seem to get a "Count" of these items.


I'm also trying to search in the box, but I'm having a hard time trying to find the right words to use :)


Thanks
 
In B2:


=SUMPRODUCT(ISNUMBER(SEARCH(A2,Sheet2!$A$2:$A$5,1))*1)


Adjust the ranges to suit and copy down the formula.
 
Hi 3G,


You can also try this:


=COUNTIF($A$8:$A$11,"*"&A2&"*")


Where A2:A10 is the Unique list while A8:A11 contains multiple items in each cell.


Regards,

Faseeh
 
Thanks folks!


Faseeh - I tried something similar to that, but didn't have the "*". Is that a wild card? Good to know I was on the same track!


Thanks!
 
Shrivallabha

If I wanted to add one more criteria to this would it look like this?


=SUMPRODUCT((ISNUMBER(SEARCH(A23,Theme_All,1))*1)*(DDR="Care:R1")*1)


Where DDR is another criteria, and 'Care:R1' is one of 5 values.


Thanks!
 
3G,


I don't think I understand your setup. If DDR is a named range then:

=SUMPRODUCT(ISNUMBER(FIND(A23,Theme_All,1))*(DDR="Care:R1"))
 
Hi 3g...


I don't know why.. but below post is look similar to me..


http://chandoo.org/forums/topic/sumproduct-question-2#post-76568


where "Software" is used instead of "Care:R1"


Regards,

Deb
 
Hi Deb -

It is similar, but a different solution (I put that in my first post of this thread). I didn't want to piggy back onto that post, as this is a different request. If mods prefer that I'd have added onto the previous post, then going forward I will do that.


Thanks!
 
Hi 3G


Can you please upload a sample file for the same.. with expected output!!

Regards,

Deb
 
You must log in or register to reply here.
Back
Top