#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 751
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default 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




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 751
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct sanders Excel Discussion (Misc queries) 2 August 18th 06 09:10 PM
Sumproduct Hunter Excel Worksheet Functions 1 August 17th 06 10:52 PM
sumproduct rhouchins Excel Worksheet Functions 1 November 1st 05 03:11 PM
sumproduct FLKULCHAR Excel Worksheet Functions 1 September 18th 05 03:36 PM
Sumproduct................. Kstalker Excel Worksheet Functions 8 September 12th 05 11:04 PM


All times are GMT +1. The time now is 05:08 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"