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