Counting if a time is within a specified range

[Tomster27]

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

 

[Mike H]

Try,

=SUMPRODUCT((A1:A8>=TIME(1,0,0))*(A1:A8<=TIME(16,0,0)))

Mike

 

[Ashish Mathur]

Hi,

Try this. C13 and C14 hold 01:00 and 16:00 respectively.

=SUMPRODUCT((D411>=C13)*(D411<=C14))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[francis]

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