Sumproduct if criteria is met in another cell

[iSig]

Hi all

I'm trying to count the number of times the word Pelvis is used in Column f, but only in the state of Florida. (answer should be 2)

I've tried using this formula with no luck
=SUMPRODUCT(--(ISNUMBER(SEARCH("pelvis",'Physician Data'!F:F))))/COUNTIFS('Physician Data'!B:B,"florida")

Please help! Thanks

 

[barry houdini]

Hello iSig, welcome to MrExcel,

Try using a wildcard with COUNTIFS like this

=COUNTIFS('Physician Data'!B:B,"Florida",'Physician Data'!F:F,"*Pelvis*")

 

[iSig]

Hello iSig, welcome to MrExcel,

Try using a wildcard with COUNTIFS like this

=COUNTIFS('Physician Data'!B:B,"Florida",'Physician Data'!F:F,"*Pelvis*")

Wow. That was quick. It works perfectly. Thank you!!