| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to I create a comma separated values file | Excel Discussion (Misc queries) | |||
| table of # of rows with unique combos of values in 4 separated col | Excel Worksheet Functions | |||
| CONCATENATE on separated values | New Users to Excel | |||
| How to get values separated with / | Excel Discussion (Misc queries) | |||
| Fixing Comma Separated Values (.csv) | Excel Discussion (Misc queries) | |||
Linear Mode
