How to best count?

[JKwan]

I have a list of items, delimited by commas. What is the best way to count this list with respect to its content? Normally, I would use SumProduct to do this, however, since the cells contain multiple items, I don't know if SumProduct will be able to count the list up.
My data looks like this basically:

Head, Ear, Foot, Stomach
Foot, Toes, Head, Stomach

Head - 2
Ear - 1
Foot - 2
Stomach - 2
and so on .....

How would I count the items without parsing the item and then look for it individually within the cell? I know how to program it, if I can do a short cut, I will take it :-).

Thanks.

[lotuxel]

Plz try this.
code:

Sub count_item()
Dim sn As Worksheet
Dim i As Long
Dim j As Long
Dim ordList As New Collection
Dim ardList As New Collection
Dim x As Long
Dim row_l As Long
Dim row_p As Long
Dim q As Long
Set wb = ActiveWorkbook
Set sn = wb.Worksheets(ActiveSheet.Name)
i = 1
row_l = sn.[a1].End(xlDown).Row
With ardList
For x = i To row_l
For j = 0 To UBound(Split(sn.Range("A" & x).Value, ","))
.Add Trim(Split(sn.Range("A" & x).Value, ",")(j))
Next j
Next x
End With
q = 1
row_p = row_l + 2
For i = 1 To ardList.Count ' - 1
For j = i + 1 To ardList.Count
If ardList(i) = ardList(j) Then
q = q + 1
sn.Cells(row_p, 1) = ardList(i) & "-" & q
ardList.Remove j
j = j - 1
End If
If j = ardList.Count Then Exit For
Next j
row_p = row_p + 1
q = 1
Next i
End Sub

[JKwan]

Thank you for producing the code. I must admit that it was not what I was after. None the less, I thank you for your gesture. I was hoping for SumProduct solution. I wanted to see if it can do the trick and I don't know if it can or possible. Also, I wanted to learn more with the built in functions within Excel as well.

Now, about the code. It is semi working. If there is only one occurance, like Ear and Toes, they were filtered out.

[JKwan]

Well, I sort of got it to work with SumProduct, however, once I added one more dimension to it, then I am stuck! Hopefully, someone can give me a nudge to get me moving! As described earlier in this thread, I wanted to count the occurance of body parts injured. I wanted to use SumProduct. I got it to work in conjuction of me adding a UDF. Now, I wanted to do a break down by a monthly count. This is where I am stuck! I can count up the total without the break down. I really hope someone can give me a push so I can do this monthly count. I am attaching the SS to illustrate

Thanks.

[p45cal]

Try in F2:
=SUMPRODUCT(($C$2:$C$4=$E2)*(ISNUMBER(SEARCH(F$1,$B$2:$B$4))))
copied across and down.
There is an inherent problem with this though, it will find partial matches, so for example Forehead will match with Head

[lotuxel]

I got new lesson.
Thanks p45cal

[JKwan]

Thanks P45, that is exactly the solution that I was looking for. By the way thanks for the heads up on the potential miss count. I did some digging, so I know how the formula works. In doing so, instead of using SEARCH function, I use FIND. This should be a better way for me to count and should eliminate the miss count of within the word (unless I am out to lunch, you never know.)

[p45cal]

The difference between SEARCH and FIND is case-sensitivity, so if you use FIND it won't find head if you're looking for Head.

[JKwan]

Yes, that is exactly what I want. Because I don't want to count forehead, when I am looking for head. I will always proper case the body parts, therefore, this should (will) eliminate my mis-count.

Thanks again for the extra cautionary explanation.