SUMIF COUNTIF To Look For More Than One Keyword

[william516]

I'm working on creating some tables that tabulate out the different types of devices but I have run into a snag when it comes to getting the sumif command to see more then one text word to look for.

Here is a quick example.
I need the sumif command to look for duct, duct detector, and sup detector. When the information is pasted into the sheet people did not use only one word, like I am forcing them to now. All three items "keywords" above are used in the same sheet but mean the same thing.

What I have so far works great for only one word but it wont let me add another. Is this even possible or will this need to be all done in VBA as well.

=COUNTIF('INITIATING DEVICES'!B7:B2499,"DUCT")


thanks

[Excel Fox]

Try this

=SUMPRODUCT(--ISNUMBER(MATCH('INITIATING DEVICES'!B7:B2499,{"DUCT","duct detector","sup detector"},0)))