2 Criteria Sumif or Sumproduct with wildcards (Tough?)

[Alkemix]

Hey all. I'm new here and think you guys are my best shot of solving my issue, so here goes:

-I have 3 relevant columns, A, B and C with 999 rows
-Column A is a status indicator "Yes" or "No" are the only options
-Coumn B a list of vendor names, which requires the wildcard example:"*Verizon*"
-Column C is the data range that needs to be summed

Right now i have this:
=SUMPRODUCT(--(A2:A999="No"),--(B2:B999="*VERIZON*"),C2:C999)

But, it seems sumproduct does not allow wildcard matches within it's "--" arrays. I am willing to try something else if possible, but I lack the excel knowledge to answer this myself.

Your expert help is greatly appreciated for this layman.

 

[Aladin Akyurek]

Gotcha, now for the theory portion...

How does my --(ISNUMBER(FIND()) array know which intersections to return to match with my other array?

It seems when I try just =ISNUMBER(FIND("Verizon",B2:B999)), I simply get FALSE.

For instance, say I want to do a uneccesarily complicated 1 criteria SUMIF. Using my original example why couldn't I do something like the following:
=IF(ISNUMBER(FIND("VERIZON",B2:B999)),SUM(C2:C999),0)


I'm just trying to wrap my head around how the =ISNUMBER(FIND()) sends the specific instances to intersect with my other array to sum the correct amounts in my data range.

Let me know if this question is irrelevant to the function because I'm trying to figure out in what other situation I could incorporate the ISNUMBER(FIND()) into my formula for correct results.

I assume that you know how SumProduct works...

ISNUMBER(FIND/SEARCH(SearchString,Range))

returns a bunch of TRUE's and FALSEs.

--ISNUMBER(FIND/SEARCH(SearchString,Range))

ends up in an array/vector of 1's and 0's for the -- bit effects the transformation from TRUE/FALSE to 1/0. These numeric vector/array is eaxctly the data type that SumProduct needs.

 

[Alkemix]

well, i had enough knowledge to get sumproduct to work as a tool for summing data ranges with multiple criteria and exact matches, but no, I did not have a real/full understanding of it until now. I thought I'd capitalize on the subject matter and get a better understanding of it rather than just get a quick fix.

Thanks again for the answers.