countif time range

Question

Hi - I am using the below formula, which I think should be correct, but it says I am using too few arguments.  what am I missing?

COUNTIF((input!C2:C500>=TIME(0,0,0))*(input!C2:C500<=TIME(0,0,59)))

this is to pull times

thanks!

Answer 1

Hi,

What you were using was seconds, TIME takes the format of

=TIME(Hours,Minutes, Seconds)

What you want I thing is times in the grouping below and note the end time for each group includes the last 60 seconds of the hour. EDIT...Actually the last 59 seconds.

let's stick with COUNTIFS, it shorter, Try this in a cell and note:-

It's all one line so paste it into the formula bar.

As written it does 00:00:00 to 00:59:59 i.e the first hour

Drag down for the second hour and third etc

=COUNTIFS(Input!$C$2:$C$500,">="&TIME(0,0,0)+TIME(0,(ROW(A1)-1)*60,0),Input!$C$2:$C$500,"<="&TIME(0,59,59)+TIME(0,(ROW(A1)-1)*60,0))

00:00:00	00:59:59
01:00:00	01:59:59
02:00:00	02:59:59
03:00:00	03:59:59
04:00:00	04:59:59
05:00:00	05:59:59
06:00:00	06:59:59
07:00:00	07:59:59
08:00:00	08:59:59
09:00:00	09:59:59
10:00:00	10:59:59
11:00:00	11:59:59
12:00:00	12:59:59
13:00:00	13:59:59
14:00:00	14:59:59
15:00:00	15:59:59
16:00:00	16:59:59
17:00:00	17:59:59
18:00:00	18:59:59
19:00:00	19:59:59
20:00:00	20:59:59
21:00:00	21:59:59
22:00:00	22:59:59
23:00:00	23:59:59

Answer 2

worked - thanks!

Answer 3

Hi,

You need to eliminate blank cells from the count

=SUMPRODUCT((Input!C2:C500>=TIME(0,0,0))*(Input!C2:C500<=TIME(0,0,59))*((Input!C2:C500<>"")))

or you can use COUNTIFS

=COUNTIFS(Input!C2:C500,">="&TIME(0,0,0),Input!C2:C500,"<="&TIME(0,0,59))

Answer 4

No - that for some reason is counting 366 worth of data which is very odd because I only have 135 items to count from!

Answer 5

Hi, try

=sumproduct((input!C2:C500>=TIME(0,0,0))*(input!C2:C500<=TIME(0,0,59)))