Count IFs Formula With Dates

Sparda142

Board Regular
Joined
Dec 19, 2018
Messages
51
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:

abcdefghijkl
1Names and Dates7/21/20197/22/20197/23/20197/24/20197/25/20197/26/20197/27/20197/28/20197/29/20197/30/20197/31/2019
2John1:00 PM-2:00 PM4:00 PM-5:00 PM2:00 PM-3:00 PM1:00 PM-2:00 PM4:00 PM-5:00 PM2:00 PM-3:00 PM1:00 PM-2:00 PM4:00 PM-5:00 PM1:00 PM-2:00 PM
3joe4:00 PM-5:00 PM1:00 PM-2:00 PM2:00 PM-3:00 PM5:00 PM-6:00 PM1:00 PM-2:00 PM1:00 PM-2:00 PM4:00 PM-5:00 PM3:00 PM-4:00 PM3:00 PM-4:00 PM
4jill4:00 PM-5:00 PM4:00 PM-5:00 PM1:00 PM-2:00 PM2:00 PM-3:00 PM5:00 PM-6:00 PM4:00 PM-5:00 PM1:00 PM-2:00 PM5:00 PM-6:00 PM5:00 PM-6:00 PM
5
67/21/2019
71:00 PM-2:00 PM=COUNTIF(B2:B4,A7)

82:00 PM-3:00 PM
93:00 PM-4:00 PM
104:00 PM-5:00 PM
115:00 PM-6:00 PM
12
13
14
157/22/2019
16=COUNTIF(C2:C4,A16)
1710:00 AM-11:00 AM
182:00 PM-3:00 PM
193:00 PM-4:00 PM
204:00 PM-5:00 PM
217:00 PM-8:00 PM

<tbody>
</tbody>
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
38,559
Office Version
365
Platform
Windows
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
Joined
Dec 19, 2018
Messages
51
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
Joined
Jun 12, 2014
Messages
38,559
Office Version
365
Platform
Windows
It maybe possible to do something with OFFSET, but I've no idea how.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,372
Messages
5,444,070
Members
405,265
Latest member
Iram

This Week's Hot Topics

Top