Absence Tracker with rolling 6 months Bradford Factor

May be

Formula:

=COUNTIF($B$4:$NC$4,"H")

"H" stand for Holiday.
Same apply with other, only you need change "H" to "S" ...... so on.

For blank

Formula:

=COUNTBLANK($B$4:$NC$4)

Try the following in cell NE4: =SUMPRODUCT(COUNTIFS(B4:ND4,NL$5:NL$10,B$2:ND$2,">="&EDATE(NM$1,-6),B$2:ND$2,"<="&NM$1))
Note that the formula may be copied down column NE without modification.
Note that the formula references cell NM1 which, for test purposes, may contain 12/31/2020 so that cell NE4 will display 3 just as it does now.
After testing you may place =TODAY() in cell NM1.
Let us know if you have any questions.

Originally Posted by JeteMc

Try the following in cell NE4: =SUMPRODUCT(COUNTIFS(B4:ND4,NL$5:NL$10,B$2:ND$2,">="&EDATE(NM$1,-6),B$2:ND$2,"<="&NM$1))
Note that the formula may be copied down column NE without modification.
Note that the formula references cell NM1 which, for test purposes, may contain 12/31/2020 so that cell NE4 will display 3 just as it does now.
After testing you may place =TODAY() in cell NM1.
Let us know if you have any questions.

Thanks JeteMc.

I moved the formula into NI4 is this is where I wanted it and if i use the date 31/12/2020 in column NM1 it returns a number for that date but if I change it to =today() the number disappears?

Also can I change a cell reference to a text reference? i.e NL5 to "s" if so how can this be added in?

thanks for your help.

The reason that the number disappears when you change NM1 to =TODAY() is that today's date is 12/19/2019 and the dates of the absences in the file are between 12/10/2020 and 12/19/2020, so they are not in the past six months, but rather in the future.
If you want the formula to reference "s":
1. Select cell NM1
2. Select the Name Box which is window furthest to the left of the formula bar
3. Type s into the name box window and press the Enter key
4. Modify the formula to read: =SUMPRODUCT(COUNTIFS(B4:ND4,NL$5:NL$10,B$2:ND$2,">="&EDATE(s,-6),B$2:ND$2,"<="&s))
Let us know if you have any questions.

In looking around at what others have done I don't feel as if the set up on the 2020 sheet is optimal for accomplishing a Bradford Score.
If the days that a person is absent are put in a table as modeled in columns A:C on Sheet 1 then the formulas are not difficult.
Note that the table has to be sorted by Name and then by Date (oldest to newest) in order for this to work.
1. Column E, which can not be part of the table, identifies instances using:

Formula:

=AND(Table1[@Date]>=EDATE(Cur_Date,-Months),COUNTIFS(Absence,Table1[@Reason])>0,OR(Table1[@Name]<>A3,WORKDAY(Table1[@Date],1,Holidays)<>B3))+0

2. Cells H3 and down display the number of days absent using: =SUMPRODUCT(COUNTIFS(Table1[Name],G3,Table1[Reason],Absence,Table1[Date],">="&EDATE(Cur_Date,-Months)))
3. Cells I3 and down display the number of instances using: =SUMIFS(E$2:E$13,Table1[Name],G3)
4. Cells J3 and down display the Bradford Score using: =I3^2*H3
5. Cell H1 contains the current date and may be changed to =TODAY() as discussed earlier
6. Cell J1 contains the number of months involved in the rollover
7. Cells N2:N4 are absence codes to be counted
8. Cells O2:O4 are holidays
Let us know if you have any questions.

I let this sit for a couple of days in hopes that someone else might provide the solution you want. Sorry not to have been of more help, however if you should change your mind about the layout just respond to this thread an I will receive a notification. I hope that you have a blessed day.

Assuming that any letter entered in a cell is an absence that should be counted try the following array entered formula:

Formula:

=SUM(IF(FREQUENCY(IF((B4:NC4<>"")*(B$2:NC$2>=EDATE(TODAY(),-6))*(B$2:NC$2<=TODAY()),COLUMN(B4:NC4)),IF((B4:NC4="")*(B$2:NC$2>=EDATE(TODAY(),-6))*(B$2:NC$2<=TODAY()),COLUMN(B4:NC4))),1))

Let us know if you have any questions.
Edit: If on the other hand you only want to count certain letters such as S, L and HD then try:

Formula:

=SUM(IF(FREQUENCY(IF(((B4:NC4=NQ$5)+(B4:NC4=NQ$6)+(B4:NC4=NQ$7))*(B$2:NC$2>=EDATE(TODAY(),-6))*(B$2:NC$2<=TODAY()),COLUMN(B4:NC4)),IF((B4:NC4="")*(B$2:NC$2>=EDATE(TODAY(),-6))*(B$2:NC$2<=TODAY()),COLUMN(B4:NC4))),1))