Counting Strings

Hey Guys

I have a Log that goes from E6:E200 for the day and i want to count how many times a string occurs, I have been using sumproduct but
people cant type or use the right grammer

Having Problems i thought i had it figured out with this

=SUM(LEN(E6:E200)-LEN(SUBSTITUTE(E6:E200,"Shop 1","")))/LEN("Shop 1")

But gets errors and when it does work wont count Multibles

the 4 Strings are Shop 1, Shop 2, Shop 3, & Shop 4 these can be used all at once also so say Shane goes to Shop 1 & 2 then Bill goes to
Shop 3 & 4 and then Kim Goes to Shop 2, 3 & 4

Try

Formula:

=SUM(COUNTIF(E6:E200,"*Shop*"&{1,2,3,4}&"*"))

Almost but adds all the Shops up Im trying to get them seperate
eg Shane goes to Shop 1 & 2 then Bill goes to Shop 3 & 4 and then Kim Goes to Shop 2, 3 & 4
Will Be Trips
Shop 1 1
Shop 2 2
Shop 3 2
Shop 4 2

---------- Post added at 09:29 AM ---------- Previous post was at 09:24 AM ----------

Nevermind some editing i worked it ou thank you so much :D <3

Try this one instead.

=COUNTIF($A$2:$C$2,"*Shop*1*")

Change 1 to 2, 3, or 4 for the remaining shops / strings.

Note that if there is any additional text at the end of the string with the same numbers then it will trip the result.

Simple example, "Shane goes to Shop 1 at 2:00", although to look at it appears obvious that 2 is time, not a shop number, it would still be counted as Shop 2.

That one works also thankyou


Nope Sorry MarvinP that isnt what im Looking for Thankou thou