Countif w/semicolon separated values
 

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

Countif w/semicolon separated values
 

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

"crazymfr" wrote:

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

Countif w/semicolon separated values
 

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.

"BobT" wrote:

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

"crazymfr" wrote:

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

Countif w/semicolon separated values
 

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

€žcrazymfr€� ezt Ã*rta:

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.

"BobT" wrote:

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

"crazymfr" wrote:

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

Countif w/semicolon separated values
 

Try this:

A1:A7 = Monday - Sunday

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

Copy down as needed.

--
Biff
Microsoft Excel MVP


"crazymfr" wrote in message
...
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.

"BobT" wrote:

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

"crazymfr" wrote:

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