Countif with Multiple Wildcard Criteria

[Weasel]

Hello,

I'm trying to come up with a formula to count that number of item's that match mulitple criteria. In order to find a match I need to look inside a cell's text and look for specific letters or numbers.

In Column A I have a bunch of numbers and in column B are a bunch of sentences. There are 10,000 or so row's of data for each column. I need a formula that will first look for a matching number from Column A and then will allow me to enter anywhere from 2 to 5 wildcard phrases to search for in column B. The formula should then count all of the matches

So for example I might want to look for the number 5405 in column A and then for the phrases "E54", "NB3", and "Heritage" in column B and give me a total count for all matches.

Any help would be greatly appreciated

[daddylonglegs]

Which version of Excel?

In 2007 or later you can use COUNTIFS

=SUM(COUNTIFS(A2:A10000,5405,B2:B10000,"*"&{"E54","NB3","Heritage"}&"*"))

[Weasel]

If have 2007 but some of the people who will use the sheet might have 2003

[NBVC]

Perhaps something like:

=SUMPRODUCT(--(A1:A10=5405),--ISNUMBER(MATCH(B1:B10,{"E54","NB3","Heritage"},0)))

adjust ranges to suit, you can replace the criteria with cell references... the { } would be replaced with a range.

[daddylonglegs]

I think the E54 etc. values appear within other text, is that right?

One way for Excel 2003 would be

=SUMPRODUCT((A2:A10000=5405)*ISNUMBER(SEARCH({"E54","NB3","Heritage"},B2:B10000)))

Note: that might double count a single row if, say E54 and Heritage both appear in the same cell in column B.....is that a possibility?

[Weasel]

Thanks for all your help