Sumproduct

S

Sandy

On Sheet2 I am trying to total the numbers on Sheet1 in the range
("C43:K43") only when "First" is the value in Range("C39:K39") and "Hit" is
the value in Range ("C40:K40"). I have tried the following to no avail:-

=SUMPRODUCT(--(Sheet1!C39:K39="First"),--(Sheet1!C40:K40="Hit"),--(Sheet1!C43:K43))

Any advice?
Sandy
 
B

bj

What errors are happening?
i would try sepatating each array function and find out where the errors
occur.
Probably in the word lists
what do you get with =countif(sheet1!c39:K39,"First")?
with ...,Hit")
=sum(...C43:K43)

there might be leading or trailing spaces in the words/
you may need to add trim(), clean() or substitute() to remove things to
identify the words.
 
H

Harlan Grove

Sandy said:
On Sheet2 I am trying to total the numbers on Sheet1 in the range
("C43:K43") only when "First" is the value in Range("C39:K39") and
"Hit" is the value in Range ("C40:K40"). I have tried the following
to no avail:-

=SUMPRODUCT(--(Sheet1!C39:K39="First"),--(Sheet1!C40:K40="Hit"),
--(Sheet1!C43:K43))
Click to expand...

What's the problem? Excel won't let you enter the formula? The formula
returns an error value? The formula doesn't return an error value but
does return the wrong result?

There's nothing wrong with the two conditional expressions, but you
may want to change the expression for the range you're summing.

=SUMPRODUCT(--(Sheet1!C39:K39="First"),--(Sheet1!C40:K40="Hit"),
Sheet1!C43:K43)
 
T

Toppers

You have replies to your previous posting:

One possible change ....

=SUMPRODUCT(--(Sheet1!C39:K39="First"),--(Sheet1!C40:K40="Hit"),Sheet1!C43:K43)
 
Top