You cannot use complete columns in this function.

=SUMPRODUCT(($F1:$F999=40)*($J1:$J999=1)*(H1:H999) )

Note I took out the quotes around the numbers 40 and 1

Use them only if the 40 and 1 are text strings.


Gord



On Fri, 13 Jan 2012 00:46:58 +0000, 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.