Count IFs Formula With Dates

[Sparda142]

Hello,


Currently i'm using the following formula below to identify how many shifts are scheduled by criteria by day. Currently i have to change the row letter for the column each time. is there a simpler way of doing this?

=COUNTIF(B2:B4,A7)

Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[TD]l[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Names and Dates[/TD]
[TD]7/21/2019[/TD]
[TD]7/22/2019[/TD]
[TD]7/23/2019[/TD]
[TD]7/24/2019[/TD]
[TD]7/25/2019[/TD]
[TD]7/26/2019[/TD]
[TD]7/27/2019[/TD]
[TD]7/28/2019[/TD]
[TD]7/29/2019[/TD]
[TD]7/30/2019[/TD]
[TD]7/31/2019[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]1:00 PM-2:00 PM[/TD]
[TD]4:00 PM-5:00 PM[/TD]
[TD]2:00 PM-3:00 PM[/TD]
[TD]1:00 PM-2:00 PM[/TD]
[TD]4:00 PM-5:00 PM[/TD]
[TD]2:00 PM-3:00 PM[/TD]
[TD][/TD]
[TD]1:00 PM-2:00 PM[/TD]
[TD]4:00 PM-5:00 PM[/TD]
[TD]1:00 PM-2:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]joe[/TD]
[TD]4:00 PM-5:00 PM[/TD]
[TD]1:00 PM-2:00 PM[/TD]
[TD]2:00 PM-3:00 PM[/TD]
[TD]5:00 PM-6:00 PM[/TD]
[TD][/TD]
[TD]1:00 PM-2:00 PM[/TD]
[TD]1:00 PM-2:00 PM[/TD]
[TD]4:00 PM-5:00 PM[/TD]
[TD]3:00 PM-4:00 PM[/TD]
[TD]3:00 PM-4:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]jill[/TD]
[TD]4:00 PM-5:00 PM[/TD]
[TD][/TD]
[TD]4:00 PM-5:00 PM[/TD]
[TD]1:00 PM-2:00 PM[/TD]
[TD]2:00 PM-3:00 PM[/TD]
[TD]5:00 PM-6:00 PM[/TD]
[TD]4:00 PM-5:00 PM[/TD]
[TD]1:00 PM-2:00 PM[/TD]
[TD]5:00 PM-6:00 PM[/TD]
[TD]5:00 PM-6:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]7/21/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1:00 PM-2:00 PM[/TD]
[TD]=COUNTIF(B2:B4,A7)

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2:00 PM-3:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3:00 PM-4:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4:00 PM-5:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]5:00 PM-6:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]7/22/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]=COUNTIF(C2:C4,A16)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]10:00 AM-11:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]2:00 PM-3:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]3:00 PM-4:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]4:00 PM-5:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]7:00 PM-8:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Not sure it's much easier, but you could use
=COUNTIF(INDEX($B$2:$K$4,0,MATCH($B$6,$B$1:$L$1,0)),A7)
Changing the value in red for each section

 

[Sparda142]

Not sure it's much easier, but you could use
=COUNTIF(INDEX($B$2:$K$4,0,MATCH($B$6,$B$1:$L$1,0)),A7)
Changing the value in red for each section

The formula would work however the actual date range is from 7/22/2019 - 12/1/2019. I would need to change the the number in red 132 times

 

[Fluff]

It maybe possible to do something with OFFSET, but I've no idea how.