need help with look-up and time rage

[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

 

[Pete_UK]

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

 

[Max]

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

 

[Noetic76]

Thanks Max and Pete, that's done the trick

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



.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thanks Max and Pete, that's done the trick






- Show quoted text -