Using COUNTIF to fill in Time vs Date grid from date+time field

[svendfj]

Cells A1:A20 of Sheet1 is a date and time field. As shown below and in the attached image, Cells A1:F5 of Sheet2 is a grid with time in Row 1 and Dates in Column A. Specifically, cells B1:F1 = 8, 9, 19, 11, 12 and cells A2:A5 = 1-Apr, 2-Apr, 3-Apr and 4-Apr.

I would like to enter formulas in cells B2:F5 of Sheet2 so that, for example, cell B2 calculates the number of cells in Column A of Sheet1 that fall between the time range of 8 AM to 9 AM on 1-Apr. Similarly, cell F5 on Sheet2 calculates the number of cells in Column A of Sheet1 that are between 11-12 AM on 4-Apr.

Many thanks for any guidance on how to best do this.

Sheet1 Cells A1:A20

4/1/23 8:21 AM

4/1/23 8:46 AM

4/1/23 9:01 AM

4/1/23 10:44 AM

4/2/23 8:55 AM

4/2/23 9:03 AM

4/2/23 11:33 AM

4/2/23 11:37 AM

4/2/23 11:44 AM

4/3/23 8:05 AM

4/3/23 8:09 AM

4/3/23 9:21 AM

4/3/23 10:22 AM

4/3/23 11:04 AM

4/4/23 8:59 AM

4/4/23 9:11 AM

4/4/23 9:21 AM

4/4/23 11:08 AM

4/4/23 11:10 AM

4/4/23 11:55 AM

Sheet2 Cells A1:F5

	8	9	10	11	12
1-Apr
2-Apr
3-Apr
4-Apr

 

[Joe4]

Assuming that all your dates in column A on each sheet are entered as valid date/times, place this formula in cell B2 on Sheet2:

=COUNTIFS(Sheet1!$A$1:$A$20,">=" & $A2+(B$1/24),Sheet1!$A$1:$A$20,"<" & $A2+((B$1+1)/24))

and copy to the rest of the range (B2:F5)

 

[svendfj]

Joe4,

Awesome. Thank you very much!

 

[Joe4]

You are welcome.
Glad I was able to help!