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:

a

b

c

d

e

f

g

h

i

j

k

l

1

Names and Dates

7/21/2019

7/22/2019

7/23/2019

7/24/2019

7/25/2019

7/26/2019

7/27/2019

7/28/2019

7/29/2019

7/30/2019

7/31/2019

2

John

1:00 PM-2:00 PM

4:00 PM-5:00 PM

2:00 PM-3:00 PM

1:00 PM-2:00 PM

4:00 PM-5:00 PM

2:00 PM-3:00 PM

1:00 PM-2:00 PM

4:00 PM-5:00 PM

1:00 PM-2:00 PM

3

joe

4:00 PM-5:00 PM

1:00 PM-2:00 PM

2:00 PM-3:00 PM

5:00 PM-6:00 PM

1:00 PM-2:00 PM

1:00 PM-2:00 PM

4:00 PM-5:00 PM

3:00 PM-4:00 PM

3:00 PM-4:00 PM

4

jill

4:00 PM-5:00 PM

4:00 PM-5:00 PM

1:00 PM-2:00 PM

2:00 PM-3:00 PM

5:00 PM-6:00 PM

4:00 PM-5:00 PM

1:00 PM-2:00 PM

5:00 PM-6:00 PM

5:00 PM-6:00 PM

5

6

7/21/2019

7

1:00 PM-2:00 PM

=COUNTIF(B2:B4,A7)

8

2:00 PM-3:00 PM

9

3:00 PM-4:00 PM

10

4:00 PM-5:00 PM

11

5:00 PM-6:00 PM

12

13

14

15

7/22/2019

16

=COUNTIF(C2:C4,A16)

17

10:00 AM-11:00 AM

18

2:00 PM-3:00 PM

19

3:00 PM-4:00 PM

20

4:00 PM-5:00 PM

21

7:00 PM-8:00 PM

<tbody>
</tbody>

 

[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.