need help with look-up and time rage

  • Thread starter Thread starter Noetic76
  • Start date Start date
N

Noetic76

hi, I have a table that looks something like this:

Unit Time
ENT 10:36:00
ENT 01:15:00
EMU 12:36:00

etc etc.

I need to be able to calculate number of entries per unit per time range, eg
the numbers of times ENT occurs between 8am and 4pm.

Any suggestions?
Thanks, Ruth
 
Try this:

=SUMPRODUCT((A1:A10="ENT")*(B1:B10>=--"8:00:00")*(B1:B10<=--"16:00:00"))

Or, you could put ENT in C1, the start time in D1 and the end time in
E1 and use:

SUMPRODUCT((A1:A10=C1)*(B1:B10>=D1)*(B1:B10<=E1))

Hope this helps.

Pete
 
One way to get it going ..
Your source data as posted assumed in A2:B2 down to row100 (say)
where col A = ENT, etc, col B = real times
StartTime/EndTime inputs are in E1:E2, eg: 8:00:00 AM, 4:00:00 PM (real times)
Codes are listed in D3 down, eg: ENT
Put in E3
=IF(COUNTA($D3,E$1:E$2)<3,"",SUMPRODUCT(($A$2:$A$100=$D3)*($B$2:$B$100>=E$1)*($B$2:$B$100<=E$2)))
Copy E3 down to return the required counts. Modify the ranges to suit the
actual extents of your data. And you could easily extend the set-up with yet
other StartTime/EndTime inputs of interest in F1:F2, G1:G2, etc. Just copy E3
across/fill down to populate. Success? celebrate it, hit YES below
 
Thanks Max and Pete, that's done the trick

Pete_UK said:
Try this:

=SUMPRODUCT((A1:A10="ENT")*(B1:B10>=--"8:00:00")*(B1:B10<=--"16:00:00"))

Or, you could put ENT in C1, the start time in D1 and the end time in
E1 and use:

SUMPRODUCT((A1:A10=C1)*(B1:B10>=D1)*(B1:B10<=E1))

Hope this helps.

Pete



.
Click to expand...
 
Back
Top