# Count IFs Formula With Dates

#### Sparda142

##### Board Regular
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>

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### Fluff

##### MrExcel MVP, Moderator
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

##### Board Regular
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

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