Thank you guys,
But I am still getting #VALUE! on the formula
Here is what I am trying to accomplish. getting the sum based on wk# & Cat#. I am on excel 2003 so I can not use sumifs.
I tried both
{=SUMPRODUCT(($A:$A=1)*($C:$C=1)*(H:H))}
=SUMPRODUCT(($A:$A=1)*($C:$C=1)*(H:H))
WK # Total Category
1 $150.00 1
1 $172.30 2
1 $7.50 3
1 $5.20 6
3 $18.33 1
1 $21.00 2
1 $9.95 3
1 $6.15 1
1 $151.65 2
1 $2.43 3
1 $100.30 6
2 $51.10 6
3 $7.00 6
4 $30.96 6
If there are other formula of getting the sum, your input is welcomed.
Quote:
Originally Posted by David Biddulph
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
|