Excel 2003: COUNTIF/SUMPRODUCT, Multiple Criteria w/Wildcard

**EricF** · 11-24-2008, 07:09 PM

I'm trying to write this but it returns a 0 when I know there are 3 records that match this criteria:

=SUMPRODUCT(('Invoice-Detail'!J2:J50="NewJob_Post.NET")*('Invoice-Detail'!H2:H50="KY_*"))

I think the problem is in the wildcard character. I don't know if I should be using COUNTIF or SUMPRODUCT or something else?

Thanks in advance for your help. I've been working on this for 2 DAYS! I give up!

PS: I'm using EXCEL 2003, not 2007!

**shg** · 11-24-2008, 07:28 PM

Maybe

=SUMPRODUCT( ('Invoice-Detail'!J2:J50="NewJob_Post.NET") * ISNUMBER(FIND("KY_", 'Invoice-Detail'!H2:H50) ) )

**daddylonglegs** · 11-24-2008, 07:37 PM

If you use "KY_*" then, in this situation, the * doesn't act as a wildcard, this is only true if the cells contain the literal string "KY_*". You can use LEFT function, i.e.

=SUMPRODUCT(--('Invoice-Detail'!J2:J50="NewJob_Post.NET"),--(LEFT('Invoice-Detail'!H2:H50,3)="KY_"))