# 2 Criteria Sumif or Sumproduct with wildcards (Tough?)

#### Alkemix

##### New Member
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.

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

##### MrExcel MVP
replace:

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

with

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

#### Alkemix

##### New Member
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.

##### MrExcel MVP
works for me - check the data.

#### Alkemix

##### New Member
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?

Last edited:

#### Alkemix

##### New Member

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!

##### MrExcel MVP

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

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

Thanks for all the answers so far!

##### MrExcel MVP
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

##### New Member
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.

Replies
6
Views
859
Replies
8
Views
2K
Replies
4
Views
1K
Replies
7
Views
352
Replies
1
Views
471

1,190,862
Messages
5,983,272
Members
439,836
Latest member
BuckyBoyRx

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back