On 13/01/2012 07:08, David Biddulph wrote:
On 13/01/2012 00:46, cupertino wrote:
Hi

I have formula below that does not work. Can some of you please take a
look for me ?

I am on Excel 2003, and need to run the sum on column H based on
criteria

column F= 40 OR an input from sheet2 cell a1
column J= 1 OR an input from sheet2 cell b1
column H= dollar amount.

=SUMPRODUCT(($F1:$F999="40")*($J1:$J999="1")*(H:H) )

I get #NUM!
I try to press ctl, shift, enter at sametime, still dont work.

Thank you for your help.

One problem is that your arrays aren't the same length. You're using 999
elements in each of columns F and J, but the whole column in H.

The first change would be to
=SUMPRODUCT(($F1:$F999="40")*($J1:$J999="1")*(H1:H 999))

Next, are you sure that the values of 40 in F and 1 in J are text values?

If they are numbers, you need to remove the text-delimiting quote marks
from the formula, and try
=SUMPRODUCT(($F1:$F999=40)*($J1:$J999=1)*(H1:H999) )

David Biddulph

Also, if there might be non-numeric values in column H, you might want
to change the formula to
=SUMPRODUCT(--($F1:$F999=40),--($J1:$J999=1),(H1:H999))
to avoid getting a #VALUE! error.

David Biddulph