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.

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.

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

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

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.

On 17/01/2012 11:40 AM, cupertino wrote:
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

Hi

Be handy if we new how your columns were formatted.

I replicated your array and it worked fine for me, that said! I
formatted Week#, Total & Category as Numeric.

Not sure why your getting an error, quite possibly your columns are Text
Values and not numeric.

I am using 2010 and I converted all the cells to Text and the formula
still returned the same answer, not sure if your version treats Text
Values that look like numbers as numbers.

I used the following: ( assuming top row was header row )
=SUMPRODUCT(($A2:$A50000=1)*($C2:$C50000=1)*($H2:$ H50000))
This returned a total of 174.48

HTH
Mick

Hi Mick,

Thank you, your formula worked. I have set all to numbers.
Now, when I replace
$A2:$A50000 with a name WK
$C2:$C50000 with a name CAT
$H2:$H50000 with a name Total

=SUMPRODUCT((WK=1)*(CAT=1)*(Total)) does not work anymore

I get #NUM!

Can I replace a range with a name, I do have 50000 lines to work with, it takes awhile to calculate, is there a faster way?

Thank you guys.

On 25/01/2012 4:54 AM, cupertino wrote:
Vacuum Sealed;1168232 Wrote:
On 17/01/2012 11:40 AM, cupertino wrote:-
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-

Hi

Be handy if we new how your columns were formatted.

I replicated your array and it worked fine for me, that said! I
formatted Week#, Total& Category as Numeric.

Not sure why your getting an error, quite possibly your columns are Text

Values and not numeric.

I am using 2010 and I converted all the cells to Text and the formula
still returned the same answer, not sure if your version treats Text
Values that look like numbers as numbers.

I used the following: ( assuming top row was header row )
=SUMPRODUCT(($A2:$A50000=1)*($C2:$C50000=1)*($H2:$ H50000))
This returned a total of 174.48

HTH
Mick


Hi Mick,

Thank you, your formula worked. I have set all to numbers.
Now, when I replace
$A2:$A50000 with a name WK
$C2:$C50000 with a name CAT
$H2:$H50000 with a name Total

=SUMPRODUCT((WK=1)*(CAT=1)*(Total)) does not work anymore

I get #NUM!

Can I replace a range with a name, I do have 50000 lines to work with,
it takes awhile to calculate, is there a faster way?

Thank you guys.




I think you will find your RangeName (Total) is the culprit causing your
problem.

Again, I replicated your table again, only this time using names.

=SUMPRODUCT((WkNo=1)*(MyCat=1)*(WkTotal))

This worked fine.

HTH
Mick.