# Help with SumIfs - Trying to create a sickness tracker that can track how many days of company sick pay someone has left, in a rolling year

I'm trying to create a spreadsheet than can track the amount of sick pay people have left in a rolling year, I'm having difficulty with with getting it to track properly however.

I have two sheets, a Leave sheet, that you enter in the date the person started sickness, and the date the sickness ended.

Column C -Name, Column D - Date from, Column E - Date to, Column F - Total day (=DAYS(E2,D2)), Column G - Work Days (SUM((F2/7)*5))

The second sheet is the employees sheet, that has the following columns

``````=if(A18<>"",sumifs(Leave!G:G,Leave!D:D,">="&\$F\$2,Leave!D:D,"<="&\$G\$2,Leave!D:D,"<=" &now(), Leave!C:C,A18, Leave!B:B,"Yes"),"")
``````

Column A is employee name
F2 has this formula - =DATE(YEAR(G2) - 1, MONTH(G2), DAY(G2)) to work out the date a year ago
G2 is just =Today()

I've taken this from another spreadsheet we used, that tracked the same thing, but for employees with their sickness in hours, so the Leave sheet was filled out differently.

Any help would be appreciated.

• I will tell you at a glance that `SUM(F2/7)*5` isn't doing what you think it is. The logic is off. If someone were sick on a Friday and the following Monday, your formula would return something like 1.4 days when in actuality it was 2 full days. And `SUM` seems unnecessary in any such equation, since you aren't actually adding anything. Commented Apr 8, 2022 at 11:58
`=sumifs(F:F,C:C,"Employee Name",D:D,"<"&today()-365,E:E,"<="&today())`