need help with look-up and time rage
 

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

need help with look-up and time rage
 

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

On Feb 24, 1:17*am, Noetic76
wrote:
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

need help with look-up and time rage
 

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$1 00=$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
--
Max
Singapore
---
"Noetic76" wrote:
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

need help with look-up and time rage
 

Thanks Max and Pete, that's done the trick

"Pete_UK" wrote:

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

On Feb 24, 1:17 am, Noetic76
wrote:
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

.

need help with look-up and time rage
 

You're welcome - thanks for feeding back.

Pete

On Feb 24, 2:16*am, Noetic76
wrote:
Thanks Max and Pete, that's done the trick



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

On Feb 24, 1:17 am, Noetic76
wrote:
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

.- Hide quoted text -

- Show quoted text -