If cell contain any part of text

[NMaxis]

Hello,

Currently on my workbook I have a formula like:
=SUMPRODUCT(COUNTIF(E2:E495,{"San Francisco CA";"Santa Rosa CA";"Los Angles CA";"Los Angeles";"Long Beach CA";"Long Beach"}))
to add to a total when this specific cites are listed. However what I want to do is to mark them when any variation of those cites are in that cell regardless of the full exit the cell.

For example:
598-Santa Rosa
Santa Rosa health center
Santa Rosa United

Would all count as well as just "Santa Rosa CA"

ANY help would be greatly appreciated, thank you.

 

[James006]

Hi,

Not sure to fully understand your constraints ...

Could you test following ...

[SIZE=2][FONT=inherit]=[/FONT][URL="https://exceljet.net/excel-functions/excel-sumproduct-function"][FONT=inherit]SUMPRODUCT[/FONT][/URL][FONT=inherit](([/FONT][FONT=inherit]--[/FONT][URL="https://exceljet.net/excel-functions/excel-exact-function"][FONT=inherit]EXACT[/FONT][/URL][FONT=inherit]("*"&[/FONT][/SIZE][COLOR=#333333]"Santa Rosa CA"&"*"[/COLOR][SIZE=2][FONT=inherit],[/FONT][/SIZE][COLOR=#333333]E2:E495[/COLOR][SIZE=2][FONT=inherit])))[/FONT][/SIZE]

Hope this will help

 

[Joe4]

I notice that none of your examples have "CA" in them, so you may need to remove that part from the formula James recommended, i.e.

[SIZE=2][FONT=inherit]=[/FONT][URL="https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-sumproduct-function"][FONT=inherit]SUMPRODUCT[/FONT][/URL][FONT=inherit](([/FONT][FONT=inherit]--[/FONT][URL="https://www.mrexcel.com/forum/redirect-to/?redirect=https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-exact-function"][FONT=inherit]EXACT[/FONT][/URL][FONT=inherit]("*"&[/FONT][/SIZE][COLOR=#333333]"Santa Rosa"&"*"[/COLOR][SIZE=2],[/SIZE][COLOR=#333333]E2:E495[/COLOR][SIZE=2])))[/SIZE]

 

[NMaxis]

I'm afraid that didn't work, it returned a result of zero for all three examples. Essentially I'm working a spreadsheet where for example there are many different names of the locations with a city (such as Santa Rosa) and I want to count each and every loaction in the column that features the cities name. But for every city in California not just Santa rosa.

For example all in one column.

Santa Rosa PA
Santa Rosa Dog park
Los Angeles airport
San Francisco museum
St. Louis library
Buffalo, NY transit.

I want the formula to pick up on "Santa Rosa", "Los Angeles", and San Francisco". And mark in the new column (so "3" would so up). Essentially if an cell contains any part of the listed city for it to count as a mark.

 

[NMaxis]

bump

 

[James006]

Hello,


Say the cell to be tested in cell A2 ... and your 6 reference items are located in D1:D6

you could test the following Array Formula:

=IFERROR(MATCH(TRUE,ISNUMBER(SEARCH($D$1:$D$6, A2)),0),"")
[/COLOR][COLOR=#333333]

Hope this will help