# SUMPRODUCT and Wild Characters

#### FalconFlyer

##### New Member
I am trying to use SUMPRODUCT to find any cells that contain "ok". The cells may contain "ok, Last Item" or "ok", all of which I want counted. My formula is:

=(SUMPRODUCT((\$K\$2:\$K\$345="ok")*(\$M\$2:\$M\$345="duplicate")))

The above will not include "ok, last item", but it does accurately count "ok".

Calculates 0, even though there are 5 matching cells with "ok" and "ok, Last Item".

How do I fix this formula to make it work for both scenarios? Do I just have to break it into 2 separate formulas?

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### BarryL

##### Well-known Member
maybe

=(SUMPRODUCT((LEFT(\$K\$2:\$K\$345,2)="ok")*(\$M\$2:\$M\$345="duplicate")))

#### JustynaMK

##### Well-known Member
Hi FalconFlyer,

=COUNTIF(A:A,"ok*")

#### Eric W

##### MrExcel MVP
Try:

=(SUMPRODUCT(--ISNUMBER(SEARCH("ok",\$K\$2:\$K\$345)),--(\$M\$2:\$M\$345="duplicate")))

wildcards don't work in SUMPRODUCT, although you can get around that using SEARCH.

#### Weazel

##### Well-known Member
possibly

=COUNTIFS(K2:K345,"ok*",M2:M345,"duplicate")

#### FalconFlyer

##### New Member
Thank you for all the suggestions. I am going to try them tonight.

#### FalconFlyer

##### New Member
Thank you all for your help.

BarryL, Eric W, and Weazel - All of your suggestions got the job done. Thank you.

JustynaMA - Unfortunately, I have to also have the "duplicates" comparison in the formula.

Replies
2
Views
300
Replies
19
Views
383
Replies
2
Views
97
Replies
3
Views
145
Replies
2
Views
233

1,190,786
Messages
5,982,916
Members
439,807
Latest member
WXM86

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