sumproduct help

[Guest]

sorry if this is the 2nd post for same question, cant find the 1st post

i have this formula for items per hour.

=SUMPRODUCT(--$A$2:$A$1000>=$B2),--($A$2:$1000<$B3))

the data is items per hour, time is military 00:00 to 23:00
formula calculates properly with exception of 23:00 which returns 0.
i have tried different variations of the formular all with the same results
thanks in advance for your help.

 

[vezerid]

What do you have in B3 when it fails? Does B3 always contain an hour
later than B2? Do you enter 00:00 in B3 when you start from 23:00?
This would explain the behavior. If so, try:

=SUMPRODUCT(($A$2:$A$1000>=$B2)*($A$2:$1000<$B3+
($B2=TIMEVALUE("23:00")))

Does this help?
Kostis Vezerides

 

[Guest]

yes, b3 always contains an hour.
i did try to start the times opposite, eg 23:00 to 00:00 i also tried 01:00
to 24:00 replacing the 00:00 with 24:00, didnt work, ( i did re label all
times to capture 24 instead of 00) same results.
i will try the formula mod to see of it works.
thanks for your time

 

[Guest]

thanks vezerid,
thank works. i also refiguerd the first formula to add an additional 00:00
to end of data source. so data starts at 00:00 ...23:00 end with 00:00. i
forgot that 23:00 data slot needs an argument in order for it to calculate.
thanks again very kindly for your time

 

[vezerid]

Glad it worked. Thanks for the feedback.

Kostis

thanksvezerid,
thank works. i also refiguerd the first formula to add an additional 00:00
to end of data source. so data starts at 00:00 ...23:00 end with 00:00. i
forgot that 23:00 data slot needs an argument in order for it to calculate.
thanks again very kindly for your time