sumproduct with ="abc*" criteria

[Guest]

Hi

I need to sum all values from K4:K1000 where H4:H1000 is earlier than 11 Jan 2004 and J4:J1000 is equal to "Cash injection -*". J4:J1000 can be "Cash injection - Mr Lim", "Cash injection - Mrs Lim" or "Cash injection - Others

Below is the formula i've used
=SUMPRODUCT(--(Misc!$H$4:$H$1000<"1/11/2004"), --(Misc!$J$4:$J$1000="Cash injection -*"), (Misc!$K4:$K1000)

Even though there should be values returned, I am getting a "-" for the results. Can i use "*" in the SUMPRODUCT function

Thank
yann

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(Misc!$H$4:$H$1000<DATE(2004,1,11)),--(ISNUMBER(SEARCH("Cash
injection",Misc!$J$4:$J$1000))),Misc!$K4:$K1000)

if H can be blank you might want this as well

=SUMPRODUCT(--(ISNUMBER(Misc!$H$4:$H$1000)),--(Misc!$H$4:$H$1000<DATE(2004,1
,11)),--(ISNUMBER(SEARCH("Cash
injection",Misc!$J$4:$J$1000))),Misc!$K4:$K1000)

the reason is that a blank cell is earlier that 01/11/2004 so excel would
return TRUE for a blank cell

--

Regards,

Peo Sjoblom

Hi,

I need to sum all values from K4:K1000 where H4:H1000 is earlier than 11

Jan 2004 and J4:J1000 is equal to "Cash injection -*". J4:J1000 can be "Cash
injection - Mr Lim", "Cash injection - Mrs Lim" or "Cash injection - Others"

Below is the formula i've used:
=SUMPRODUCT(--(Misc!$H$4:$H$1000<"1/11/2004"), --(Misc!$J$4:$J$1000="Cash

injection -*"), (Misc!$K4:$K1000))

Even though there should be values returned, I am getting a "-" for the

results. Can i use "*" in the SUMPRODUCT function?

 

[Guest]

Thanks Peo

It works great

yann