=SUMPRODUCT(--(Sheet1!B2:B200="D"),--(Sheet1!C2:C200=4),D2:D200)

When I use this formula, the result is 0.

A B C D

1 D 4 2
2 B 2 3
3 D 5 5
4 A 2 2

I did not finish b4 posting.

if you look at the example that i used, the result with this formula should
be 2. But again I am getting 0. It's supposed to find where column B has a
"D" and then look in the same row over to column C and if there is a "4"
there, then sum the hours in column D

Thank You
Brian

"Brian" wrote:

=SUMPRODUCT(--(Sheet1!B2:B200="D"),--(Sheet1!C2:C200=4),D2:D200)

When I use this formula, the result is 0.

A B C D

1 D 4 2
2 B 2 3
3 D 5 5
4 A 2 2

It is working fine with me.


--
a7n9


------------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149
View this thread: http://www.excelforum.com/showthread...hreadid=562563

could you set my example up on sheet1 while placing the formula in sheet2,
you should then see that the result is zero. I need the formula to be on a
seperate sheet.

Thank You
Brian

"a7n9" wrote:


It is working fine with me.


--
a7n9


------------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149
View this thread: http://www.excelforum.com/showthread...hreadid=562563

Brian,

Because the # D2:D200 # is not qualified as being in sheet 1 XL will assume
that it is in the active sheet. Try:

=SUMPRODUCT(--(Sheet1!B2:B200="D"),--(Sheet1!C2:C200=4),Sheet1!D2:D200)

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Brian" wrote in message
...
=SUMPRODUCT(--(Sheet1!B2:B200="D"),--(Sheet1!C2:C200=4),D2:D200)

When I use this formula, the result is 0.

A B C D

1 D 4 2
2 B 2 3
3 D 5 5
4 A 2 2

Brian

This works for me.

=SUMPRODUCT(--(Sheet1!B2:B200="D"),--(Sheet1!C2:C200=4),Sheet1!D2:D200)


Gord Dibben MS Excel MVP

On Tue, 18 Jul 2006 11:32:02 -0700, Brian
wrote:

could you set my example up on sheet1 while placing the formula in sheet2,
you should then see that the result is zero. I need the formula to be on a
seperate sheet.

Thank You
Brian

"a7n9" wrote:


It is working fine with me.


--
a7n9


------------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149
View this thread: http://www.excelforum.com/showthread...hreadid=562563