Did my earlier message not get through?
The number of hours in the TIME function can't be more than 23.

Also, I don't see why you are using TEXT for the time condition if the
column E figures are actual times, rather than text.
--
David Biddulph

"capt" wrote in message
...
Thanks Mike,
It works well as follows:
=SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TEXT(TIME(40,20,0),"hh:mm")),$D$10: $D$5000)

But when I modify it for total hours above 41:40 it doesnt work.
=SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N2),--($E$10:$E$5000TEXT(TIME(41,40,0),"hh:mm")),$D$10: $D$5000)

Is there something I doing wrong?

--
capt


"Mike H" wrote:

capt,

This mod worked for me:-
=SUMPRODUCT(--($A$10:$A$20=Sheet2!$P$3),--($A$10:$A$20<=Sheet2!$R$3),--($B$10:$B$20=N3),--($E$10:$E$20<TEXT(TIME(40,30,0),"hh:mm")),$D$10:$D $20)

Note I shortened the ranges to test the formula by selecting eacch
section
in turn in the formula bar and tapping F9 which told me how the elements
of
the array were evaluating true,False etc.

Select this bit and tap F9
$A$10:$A$20=Sheet2!$P$3

If the fix provided doesn't work then its probably your data and testing
the
formula should show the problem.

Mike

"capt" wrote:

Hi,
I have the following formula:

=SUMPRODUCT(--($A$10:$A$5000=Sheet2!$P$3),--($A$10:$A$5000<=Sheet2!$R$3),--($B$10:$B$5000=N3),--($E$10:$E$5000<TIME(41,40,0)),$D$10:$D$5000)

What its suppose to do is to filters a criteria (col B) between two
dates
(col A), it then totals all the hours for that month under 41 hours and
40
minutes.

I have a running total for that month on column E.
Sheet2!$P$3 and Sheet2!$R$3 are the two dates.

I get a figure but not the correct one.

Can anybody help?

--
capt