Countif w/semicolon separated values

[crazymfr]

I have aggregated data in columns that were imported from web forms and are
separated by semicolons. The questions was select your preferred day of week
with a select all that apply option. Any time someone chose more than one
day, it aggregates the response together in the same cell (ie choosing Monday
and Thursday returned a response of "Monday; Thursday" in B1...etc.)

I want to be able to count each instance of Monday regardless of what other
days per response.

Monday - Total
Tuesday - Total
Wednesday - Total
Thursday - Total
Friday - Total.

Below is a sample of data. Please help me! Thanks you.

Wednesday
Monday; Tuesday; Wednesday; Thursday
Tuesday; Thursday; Friday
Tuesday; Wednesday; Thursday
Monday
Monday; Tuesday; Wednesday; Thursday
Thursday
Tuesday; Wednesday
Tuesday; Wednesday; Thursday
Tuesday; Wednesday; Thursday
Monday; Wednesday; Thursday
Monday; Wednesday; Friday
Tuesday; Friday
Thursday
Monday; Wednesday
Tuesday; Wednesday

 

[BobT]

Countif(range of cells, "*Monday*")
Countif(range of cells, "*Tuesday*")
etc.

 

[crazymfr]

Thank you. This works.

Is there any way to preserve cell references instead of direct quoted text?

Your solution will work for me, but it would be a lot easier if I could
reference Monday as a cell reference.

Let me know, but thank you for this solution.

 

[Stefi]

If you put days in F1,F2 then
=SUMPRODUCT(--(ISERROR(SEARCH(F1,$B$1:$B$16))))
Regards,
Stefi

„crazymfr†ezt írta:

 

[T. Valko]

Try this:

A1:A7 = Monday - Sunday

=COUNTIF(range,"*"&A1&"*")

Copy down as needed.