Sent this in ages ago but it never appeared in the group so here we go again
:-)

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

Strange thing - I rebooted excel and retyped exactly the same formula and
now it works. What it did before was show up as though it was text in the
cell - weird
Sandy

"Sandy" wrote in message
...
Sent this in ages ago but it never appeared in the group so here we go
again :-)

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

FYI as the last range is numbers, you don't need to coerce

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sandy" wrote in message
...
Strange thing - I rebooted excel and retyped exactly the same formula and
now it works. What it did before was show up as though it was text in the
cell - weird
Sandy

"Sandy" wrote in message
...
Sent this in ages ago but it never appeared in the group so here we go
again :-)

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

I assume by "coercing" you are referring to the two minus signs back to back
and the additional brackets - never did fully understand what they did :-)
Sandy

"Bob Phillips" wrote in message
...
FYI as the last range is numbers, you don't need to coerce

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Sandy" wrote in message
...
Strange thing - I rebooted excel and retyped exactly the same formula and
now it works. What it did before was show up as though it was text in the
cell - weird
Sandy

"Sandy" wrote in message
...
Sent this in ages ago but it never appeared in the group so here we go
again :-)

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