SUMPRODUCT help needed

[cupertino]

Hi

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

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

column F= 40 OR an input from sheet2 cell a
column J= 1 OR an input from sheet2 cell b
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

 

[Gord Dibben]

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

 

[David Biddulph]

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:H999))

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

 

[David Biddulph]

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:H999))

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

 

[cupertino]

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.

 

[Vacuum Sealed]

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

 

[cupertino]

H

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 Tex

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.4

HT
Mic

Hi Mick

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

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

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

 

[Vacuum Sealed]

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.