| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif with multiple text values in cell
I have a column of state abbreviations and am simply trying to count how many
times each state appears in the column. Unfortunately, there are some cells in the column that contain two or more states. Countif doesn't like that (i.e if VA is in one cell and "MD VA NY" is in another, it doesn't count VA twice). What do I do? This isn't the only data in my sheet, so adding columns would be...messy. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif with multiple text values in cell
hi
=countif(A1:A50,"*VA*") use the wildcard * in the search. adjust range to suit. Regards FSt1 "Rothman" wrote: I have a column of state abbreviations and am simply trying to count how many times each state appears in the column. Unfortunately, there are some cells in the column that contain two or more states. Countif doesn't like that (i.e if VA is in one cell and "MD VA NY" is in another, it doesn't count VA twice). What do I do? This isn't the only data in my sheet, so adding columns would be...messy. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif with multiple text values in cell
Works like a charm! Thanks!
"FSt1" wrote: hi =countif(A1:A50,"*VA*") use the wildcard * in the search. adjust range to suit. Regards FSt1 "Rothman" wrote: I have a column of state abbreviations and am simply trying to count how many times each state appears in the column. Unfortunately, there are some cells in the column that contain two or more states. Countif doesn't like that (i.e if VA is in one cell and "MD VA NY" is in another, it doesn't count VA twice). What do I do? This isn't the only data in my sheet, so adding columns would be...messy. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif with multiple text values in cell
Try this:
=COUNTIF(A1:A10,"*Va*") Or, use a cell to hold the criteria: C1 = Va =COUNTIF(A1:A10,"*"&C1&"*") If a cell might hold: MD VA VA And you need to count that as 2 then you'll need a different formula. Let us know if that's the case. -- Biff Microsoft Excel MVP "Rothman" wrote in message ... I have a column of state abbreviations and am simply trying to count how many times each state appears in the column. Unfortunately, there are some cells in the column that contain two or more states. Countif doesn't like that (i.e if VA is in one cell and "MD VA NY" is in another, it doesn't count VA twice). What do I do? This isn't the only data in my sheet, so adding columns would be...messy. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding multiple values in one column based on multiple values of the same value (text) in another column | Excel Discussion (Misc queries) | |||
| Countif Multiple with text | Excel Worksheet Functions | |||
| COUNTIF formula multiple text exclusions | Excel Discussion (Misc queries) | |||
| using =COUNTIF to count two text values | Excel Worksheet Functions | |||
| Using COUNTIF to check values in multiple columns | Excel Worksheet Functions | |||
Linear Mode
