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.

 

[PaddyD]

replace:

--(B2:B999="*VERIZON*")

with

--(isnumber(find("VERIZON",b2:b999)))

 

[Alkemix]

replace:

--(B2:B999="*VERIZON*")

with

--(isnumber(find("VERIZON",b2:b999)))

Just tried:
=SUMPRODUCT(--(A2:A999="No"),--(ISNUMBER(FIND("VERIZON",B2:B999))),C2:C999)

and it did not yield a sum, which I know the answer to be 18.

 

[PaddyD]

works for me - check the data.

 

[Alkemix]

Strange, i tried a test sheet with only 3 columns, mind that the source data I'm using has some 80 columns, and it does in fact work, but any reason why it wouldn't work on my source data?

Here is the ACTUAL string I'm using, no longer referring to my test scenario above, but still same conditions/criteria apply:

=SUMPRODUCT(--(CO2:CO406="No"),--(ISNUMBER(FIND("VERIZON",E2:E406))),J2:J406)

Am I just missing something very elementary in my above formula?

 

[Alkemix]

Okay, regardless, problem solved, thanks for your help paddy.

Now that it's been solved, i have another Question regarding this and possible future scenarios for my excel usage...

Why is it that we use ISNUMBER rather than ISTEXT in this situation? Also, what usage is ISNUMBER used for in fact... is it simply to find any matching criteria within a single cell?... for instance if I wanted to reference/evaluate a cell with a formula in it, can I use ISNUMBER to find/match a specific part of that string as well?

Let me know if that doesn't make sense.

Thanks again!

 

[Aladin Akyurek]

Okay, regardless, problem solved, thanks for your help paddy.

Now that it's been solved, i have another Question regarding this and possible future scenarios for my excel usage...

Why is it that we use ISNUMBER rather than ISTEXT in this situation? Also, what usage is ISNUMBER used for in fact... is it simply to find any matching criteria within a single cell?... for instance if I wanted to reference/evaluate a cell with a formula in it, can I use ISNUMBER to find/match a specific part of that string as well?

Let me know if that doesn't make sense.

Thanks again!

FIND (also SEARCH) returns numbers (positions) when successful, #VALUE! otherwise. ISNUMBER, appropriately, tests what FIND returns with as result TRUE if it meets a number, otherwise FALSE.

 

[Alkemix]

Wouldn't it have returned FALSE for every instance since FIND is returning Text?

Thanks for all the answers so far!

 

[Aladin Akyurek]

Wouldn't it have returned FALSE for every instance since FIND is returning Text?

Thanks for all the answers so far!

FIND is not returning text. What it returns is a number that indicates at which position the text it is looking for is located...

Example:

A1: Alkemix is at home

=FIND("mix",A1)

will yield 5, the position at which "mix" starts.

 

[Alkemix]

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.