sumproduct help
 

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.
--
dr350x

sumproduct help
 

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

On Jun 7, 6:21 pm, dr350x wrote:
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.
--
dr350x

sumproduct help
 

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
--
dr350x


"vezerid" wrote:

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

On Jun 7, 6:21 pm, dr350x wrote:
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.
--
dr350x

sumproduct help
 

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
--
dr350x


"vezerid" wrote:

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

On Jun 7, 6:21 pm, dr350x wrote:
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.
--
dr350x

sumproduct help
 

Glad it worked. Thanks for the feedback.

Kostis

On Jun 8, 6:14 pm, dr350x wrote:
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
--
dr350x

"vezerid" wrote:
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

On Jun 7, 6:21 pm, dr350x wrote:
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.
--
dr350x