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!
Last edited by EricF; 11-24-2008 at 08:40 PM.
Maybe
=SUMPRODUCT( ('Invoice-Detail'!J2:J50="NewJob_Post.NET") * ISNUMBER(FIND("KY_", 'Invoice-Detail'!H2:H50) ) )
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
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_"))
Thanks daddylonglegs. Your response was 100% right on - worked perfect!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks