# Count of a persons name on a 30 day rolling period?

#### kemalwhitham97

##### New Member
Hello,

I am trying to develop and attendance sheet for my company which has a 3 call-offs/early leave policy, that is evaluated on a 30 day rolling period. I am trying to develop a formula to tell me if a write-up is needed, based on if the person has left early/come in late/called off 3 times within 30 days. I am currently using the formula
Excel Formula:
``=COUNTIFS(B5:B999,">="&TODAY()-30,B5:B999,"<="&TODAY())``
to evaluate the rolling period but am struggling nest it with the count function based on peoples names. Any help would be much appreciated.

 Name Date Given By Comments Write-Up Needed? Name1 Manager 1 Name1 1/1/21 Manager 1 =COUNTIFS(B5:B999,">="&TODAY()-30,B5:B999,"<="&TODAY()) Name2 Manager 2 Name1 1/2/21 Manager 2 29 Name3 Manager 3 Name4 1/3/21 Manager 3 28 Name4 Name6 1/4/21 Manager 3 27 Name5 Name4 1/5/21 26 Name6 Name9 1/6/21 Manager 2 25 Name7 Name5 1/7/21 24 Name8 Name5 1/8/21 23 Name9 Name6 1/9/21 22 Name10 Name7 1/10/21 21 Name11 Name2 1/11/21 20 Name12 Name17 1/12/21 19 Name13 Name18 1/13/21 18 Name14 Name1 1/14/21 17 Name15 1/15/21 16 Name16 1/16/21 15 Name17 1/17/21 14 Name18 1/18/21 13 Name19

Thanks,

#### Attachments

• Attendance sheet.PNG
31.9 KB · Views: 3

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Excel Formula:
``=COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())``

#### kemalwhitham97

##### New Member
Hi & welcome to MrExcel.
Excel Formula:
``=COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())``
I appreciate the reply, That formula seems to just add a 1 or a 0 into the cell if the person was out 30 days prior.

 Name Date Given By Comments Write-Up Needed? Name1 Manager 1 Name1 1/1/21 Manager 1 1 Name2 Manager 2 Name1 1/2/21 Manager 2 1 Name3 Manager 3 Name1 1/3/21 Manager 3 1 Name4 Name1 2/4/21 Manager 3 0 Name5 Name1 2/5/21 0 Name6 Name1 2/6/21 Manager 2 0 Name7 Name5 2/7/21 0 Name8 Name5 2/8/21 0 Name9 Name6 2/9/21 0 Name10 Name7 2/10/21 0 Name11 Name2 2/11/21 1 Name12 Name17 2/12/21 0 Name13 Name18 2/13/21 0 Name14 Name1 2/14/21 0 Name15 2/15/21 0 Name16 2/16/21 0 Name17 2/17/21 0 Name18 2/18/21 0 Name19 2/19/21 0 2/20/21 0 2/21/21 0

Ideally I am looking for the formula to look at the past 30 days (rolling from today) and count the number of times the name occurs. If that name occurs 3 or more times it will output "Yes" into the "Write-Up Needed?" cell and if not then it will output "No"

Hopefully this was helpful in clarifying.

Thanks

#### Fluff

##### MrExcel MVP, Moderator
With the data you have posted nobody appeared more than once in the last 30 days.
+Fluff 1.xlsm
ABCDE
1
2
3
5Name101/01/2021Manager 1Yes
6Name102/01/2021Manager 2Yes
7Name103/01/2021Manager 3Yes
8Name104/01/2021Manager 3Yes
9Name105/01/2021Yes
10Name106/02/2021Manager 2Yes
11Name507/02/2021No
12Name508/02/2021No
13Name609/02/2021No
14Name710/02/2021No
15Name211/02/2021No
16Name1712/02/2021No
17Name1813/02/2021No
18Name114/02/2021Yes
1915/02/2021No
2016/02/2021No
2117/02/2021No
2218/02/2021No
2319/02/2021No
2420/02/2021No
2521/02/2021No
Data
Cell Formulas
RangeFormula
E5:E25E5=IF(COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())>2,"Yes","No")

#### kemalwhitham97

##### New Member
With the data you have posted nobody appeared more than once in the last 30 days.
+Fluff 1.xlsm
ABCDE
1
2
3
5Name101/01/2021Manager 1Yes
6Name102/01/2021Manager 2Yes
7Name103/01/2021Manager 3Yes
8Name104/01/2021Manager 3Yes
9Name105/01/2021Yes
10Name106/02/2021Manager 2Yes
11Name507/02/2021No
12Name508/02/2021No
13Name609/02/2021No
14Name710/02/2021No
15Name211/02/2021No
16Name1712/02/2021No
17Name1813/02/2021No
18Name114/02/2021Yes
1915/02/2021No
2016/02/2021No
2117/02/2021No
2218/02/2021No
2319/02/2021No
2420/02/2021No
2521/02/2021No
Data
Cell Formulas
RangeFormula
E5:E25E5=IF(COUNTIFS(A:A,A5,B:B,">="&TODAY()-30,B:B,"<="&TODAY())>2,"Yes","No")
Works great! Thanks!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
8
Views
206
Replies
2
Views
289
Replies
4
Views
336
Replies
9
Views
641
Replies
14
Views
166

1,127,884
Messages
5,627,465
Members
416,249
Latest member
yogaraj IND

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back