Formula problem with time values

  • Thread starter Thread starter GARY NEUBAUER
  • Start date Start date
G

GARY NEUBAUER

We are having a problem creating a formula for summarizing data- ca
anyone help? Column A is a date field, e.g. 11/19/03, 11/20/03
repeating for a month. Column B is the day of the month, e.g. Sunday
Monday, etc., and column C is a time-interval column displaying time a
12:00 AM, 1:00 AM, etc., repeating for a 24 Hour period. Column D has
numeric value, and column E has a numeric switch number. We are tryin
to total the D values for all of the switches in column E within
specific time period, e.g. 1:00 AM. Excel does not seem to recogniz
time values. We would like not to have to change the time values int
numeric values. The columns run into the 7500 value range - we don'
want to have to do continual sorts to get totals. Any help in creatin
a formula where we could sort on time values would be appreciated
 
Since you didn't mention any specific switch I assume something like this
can work,

=SUMPRODUCT(--(C2:C200=--"01:00"),D2:D200)

if you want for a 01:00 - 02:00

=SUMPRODUCT(--(C2:C200>=--"01:00"),--(C2:C200<=--"02:00"),D2:D200)

and yes, the time values have to be numeric but if they are entered as 01:00
AM/PM they should be
 
Back
Top