sumproduct with ="abc*" criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

yann said:
Hi,

I need to sum all values from K4:K1000 where H4:H1000 is earlier than 11
Click to expand...
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
Click to expand...
injection -*"), (Misc!$K4:$K1000))
Even though there should be values returned, I am getting a "-" for the
Click to expand...
results. Can i use "*" in the SUMPRODUCT function?
 
Back
Top