Count list of whole numbers within a cell

[gkc1953]

I have a spreadsheet that tracks people's activities over several weekends. Column A contains their name, column B contains activity one, column C contains activity two, and so on. The weekend number is typed into column B, C, D, depending upon the activity. If a person does the same activity over multiple weekends, the weekend numbers are typed in column B separated by columns.

Example:
A1 = Bob B1= 1, 2, 13, 14 C1 = 5

I am looking for a way to count the number of weekends a person has worked any particular activity. I need a way to calculate that Bob has worked activity one four times (from B1) and activity two (from C1) once. I know there should be a way using the LEN function but all I seem to do is count digits, not whole numbers. Any ideas?

[mehmetcik]

count the commas and then add one.

Please Login or Register  to view this content.

[Sam Capricci]

something like this? =LEN(B1)-LEN(SUBSTITUTE(B1,",",""))+1+(LEN(C1)-LEN(SUBSTITUTE(C1,",",""))+1)
how many columns does it have to be, and is the correct to your example answer 5?

[gkc1953]

Sambo, great response, but I had to make a minor tweak in case one of the columns was blank.

=(IF(ISBLANK($B1),0,LEN(TRIM($B1))-LEN(SUBSTITUTE(TRIM($B1),",",""))+1))+(IF(ISBLANK($C1),0,LEN(TRIM($C1))-LEN(SUBSTITUTE(TRIM($C1),",",""))+1))

Thanks for your help!

[Sam Capricci]

Great, glad I could help. Wasn't much different than what mehmetcik gave you though.
If that solved your issue please don't forget to mark the post as solved using the thread tools at the top of this post.
AND, thank you for the rep!