Hello,
I want to count items which seperated by comma in a cell, by product group.
How can I do that?
Ex:
Cell D6: Apple, Banana, Carrot
Fruit List:
Apple
Banana
Orange
Vegetable List:
Potato
Tomato
Carrot
I want to see in E6 and F6 cells that D6 includes 2 fruit, 1 vegetable respectively.
I also added sample workbook.
Thanks in advance
In E6
=SUMPRODUCT(--ISNUMBER(SEARCH(J$6:J$8,$D6)))
copy across and down
Counts TYPE not Occurrence so D7 which has Potato twice [Potato, Potato, Tomato, Orange] will count as 1
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks it is worked!
But, I need to count recurrent values too. Maybe I can add a number near the value in a paranthesis like "Potato (2)". How can we achieve this?
Excel 2017? is that a Mac or a PC version?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
This will work with a PC, but not a Mac...
=SUMPRODUCT(--ISNUMBER(SEARCH(FILTERXML("<A><B>"&SUBSTITUTE($D6,", ","</B><B>")&"</B></A>","//B"),TRANSPOSE(J$6:J$8))))
I use PC and it worked fine.
You are a Genious!
I am so grateful!!
Nahh. i've just done it before!
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks