Sumproduct problem.

[capt]

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?

 

[Mike H]

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

 

[David Biddulph]

Check in Excel help for the TIME function, and you'll see its limitations.

 

[capt]

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$5000>TEXT(TIME(41,40,0),"hh:mm")),$D$10:$D$5000)

Is there something I doing wrong?

 

[Mike H]

Put your time in a cell formatted as [hh]:mm and then this formulsa

=SUMPRODUCT(--($A$10:$A$20>=Sheet2!$P$3),--($A$10:$A$20<=Sheet2!$R$3),--($B$10:$B$20=N3),--($E$10:$E$20>F10),$D$10:$D$20)

Mike

 

[David Biddulph]

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.

 

[capt]

Thanks Mike,
I will try that, but at the moment it seems to have done the job.

 

[David Biddulph]

The message you've replied to is mine, not Mike's.
If you are trying to use the TIME function with an hours figure greater than
23 (and you had 41 in your example), then you'll get the wrong result.