Help with IF AND function using a wildcard

[JWMcCrary]

I am trying to modify a spreadsheet that was built a few years ago, I am very rusty and need some help. I have a range of 5000 cells that I need it to find either of 2 counties (McPherson or McPherson2), if either is true it also needs to verify that in another range of 5000 cells the coresponding cell contains a 1, if that is true it puts a 1 in the cell.

Here is my formula

=IF(AND(Export_Tract_Info.xlsx!$A$2:$A$5000="McPherson*", M2:M5001=1),1,0)

I'm not getting any errors and if I take the * out it will find McPherson and work fine, I need it to look for both.

The following formula works flawlessly in another part of the sheet, I don't understand why the above fails.
=IF(A12<>"",SUMIF(Export_Tract_Info.xlsx!$A$2:$A$5000,"McPherson*",M2:M5001),"")

Thanks in advance for any help

[Melvinrobb]

Wildcards only work when you have your range and your criteria separated by "," not by "=".
I think you want this formula:
=IF(AND(countif(Export_Tract_Info.xlsx!$a2,"McPherson*")=1,M2=1),1,0)

[dogberry]

Hi

Not sure if this will work as I havent got a sample file but try changing AND to SEARCH that should find them for you.


Chris