Problem with COUNTIF and wild character

[alex_a]

Good Morning,

I have the follwoing data in column C and D. in the C column i have multiple codes all of them start with the word QC. I need to count the numebr of "yes" in the column D. I am using an array formula like this:
SUMPRODUCT(C1:C20="QC*")*(D1:D20="Yes"). I get an erroo message (#Name!). What is wrong with this formula? Thanks.

 

[Jonmo1]

Sumproduct won't support wildcard use like that.

And you ENDED the sumproduct with the first ), which left (D1:D20="Yes)" as a standalone function..
SUMPRODUCT(C1:C20="QC*")*(D1:D20="Yes")

Which version of Excel are you using?
In XL2007 we have COUNTIFS
=COUNTIFS(C1:C20,"QC*",D1:D20,"Yes")

 

[alex_a]

i am using 2003 version.

 

[taurean]

COUNTIFS will be faster.

But if you intend to use SUMPRODUCT then you will have to use construct like below:
=SUMPRODUCT((LEFT(C1:C20,2)="QC")*(D1:D20="Yes"))

 

[CodeNinja]

ADVERTISEMENT

If you know it always starts with a QC then just use the left function...
Also, you need to convert the trues and falses to 0's and 1's.

=SUMPRODUCT(--(LEFT(C1:C20,2)="QC")*(--(D1:D20="Yes")))

 

[taurean]

If you know it always starts with a QC then just use the left function...
Also, you need to convert the trues and falses to 0's and 1's.

=SUMPRODUCT(--(LEFT(C1:C20,2)="QC")*(--(D1:D20="Yes")))

Double unaries are not required as * [multiplication] will coerce it to numbers.

 

[alex_a]

ADVERTISEMENT

COUNTIFS will be faster.

But if you intend to use SUMPRODUCT then you will have to use construct like below:
=SUMPRODUCT((LEFT(C1:C20,2)="QC")*(D1:D20="Yes"))

Thanks. It worked liek a charm.

 

[alex_a]

Thanks. It worked liek a charm.

I hav eanother question: If I want ato add another condition such as if the wild character is QC or blank, how can I modify the sumproduct or countif statement? Thanks.

 

[jai9]

Maybe,

=SUMPRODUCT((LEFT(C1:C10,2)="QC")*(E1:E10="Yes"))+SUMPRODUCT((C1:C10="")*(E1:E10="Yes"))

Jai

 

[Aladin Akyurek]

I hav eanother question: If I want ato add another condition such as if the wild character is QC or blank, how can I modify the sumproduct or countif statement? Thanks.

=SUMPRODUCT(--ISNUMBER(MATCH(LEFT(C1:C10,2),{"QC",""},0)),--(E1:E10="Yes"))