Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting if a time is within a specified range
I have a list of times (part of which is included below). I want to be able
to count them if they fall between the times of 01:00 and 16:00 and ignore them if they fall outside those times. I have tried COUNTIF but have got very badly stuck. I would be very grateful for any help 20:26 10:15 23:24 1:45 15:23 15:57 5:01 14:18 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting if a time is within a specified range
Try,
=SUMPRODUCT((A1:A8=TIME(1,0,0))*(A1:A8<=TIME(16,0 ,0))) Mike "Tomster27" wrote: I have a list of times (part of which is included below). I want to be able to count them if they fall between the times of 01:00 and 16:00 and ignore them if they fall outside those times. I have tried COUNTIF but have got very badly stuck. I would be very grateful for any help 20:26 10:15 23:24 1:45 15:23 15:57 5:01 14:18 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting if a time is within a specified range
Hi,
Try this. C13 and C14 hold 01:00 and 16:00 respectively. =SUMPRODUCT((D4:D11=C13)*(D4:D11<=C14)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Tomster27" wrote in message ... I have a list of times (part of which is included below). I want to be able to count them if they fall between the times of 01:00 and 16:00 and ignore them if they fall outside those times. I have tried COUNTIF but have got very badly stuck. I would be very grateful for any help 20:26 10:15 23:24 1:45 15:23 15:57 5:01 14:18 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting if a time is within a specified range
try this array formula, confirm by Ctrl, Shift and Enter
=SUM((A2:A9=TIME(1,0,0))*(A2:A9<=TIME(16,0,0))) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Tomster27" wrote: I have a list of times (part of which is included below). I want to be able to count them if they fall between the times of 01:00 and 16:00 and ignore them if they fall outside those times. I have tried COUNTIF but have got very badly stuck. I would be very grateful for any help 20:26 10:15 23:24 1:45 15:23 15:57 5:01 14:18 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting calls received by time range by year | Excel Worksheet Functions | |||
Counting from one range to another range, multiple criteria | Excel Discussion (Misc queries) | |||
Counting each time text appears in range??? | Excel Worksheet Functions | |||
Counting a range | Excel Worksheet Functions | |||
counting a cell range within a range | Excel Worksheet Functions |