sumproduct w/ wild card question

[scorpia]

I am having trouble getting the wild card "*" to work with SUMPRODUCT.

For instance, I want to count the number of cells within a range of employee names where the name/text contains the "word" TBD (among other conditions).

Lets say Column A is employee names, Column B is the employee's division.

I am trying
=SUMPRODUCT((A2:A15="*"&"TBD"&"*")*(B2:B15="HR"))

There is a third condition - but it is the wild card that is producing a name error. This is also only part of the entire formula. What am I doing wrong?

 

[RoryA]

SUMPRODUCT does not accept wildcards. You need to use SEARCH (or FIND if you want it case sensitive):
=SUMPRODUCT(ISNUMBER(SEARCH("TBD",A2:A15))*(B2:B15="HR"))

 

[scorpia]

I tried that first and it didn't work - also got the #NAME error. I will try it again, just in case.

 

[scorpia]

I am not getting the error now but the calculation is wrong. It is returning 1, when the correct result is 5.

 

[RoryA]

Hard to explain without seeing the exact formula and data!

 

[scorpia]

I did get it to work - my problem is combining with a 3rd criteria and nesting it within a =IF. Its not working that way or as an =IF(AND with the SUMPRODUCT with just the 2 criteria. I know its hard to help without the data - but do you have a sample of how an equation like this could work? I basically want to the count the number of times 3 criteria are met - but only if the Weekend Date in my column header is greater than today's date.

I have the =IF(B$2>Today(),SUMPRODUCT yadda yadda.

 

[RoryA]

It would be basically the same SUMPRODUCT:
=IF(B$2>TODAY(),SUMPRODUCT(ISNUMBER(SEARCH("TBD",A2:A15))*(B2:B15="HR")*(range3=criteria3)),0)

 

[scorpia]

Thank you so much - I had a missing paren. Surprised the error check didn't pick it up. I am getting one anomaly however - of all the cells I updated with this formula only one is returning a value different that what it should. I'm getting a zero where there should be a one. Going to try and solve this but thanks again for your help!