SUMPRODUCT and Wild Characters

[FalconFlyer]

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".

=(SUMPRODUCT(($K$2:$K$345="ok*")*($M$2:$M$345="duplicate"))) - Added "*"
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?

Thank you in advance.

 

[BarryL]

maybe

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

 

[JustynaMK]

Hi FalconFlyer,

How about simply using:
=COUNTIF(A:A,"ok*")

 

[Eric W]

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]

possibly

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

 

[FalconFlyer]

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

 

[FalconFlyer]

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.