Help to SUMIF past 12 months

[MissG]

Hi
My ambition is far outweighing my skill to achieve and wondered if i could get some help please.

I have a spreadsheet that is looking up absence periods and can get from that a total, however, i also want to be able to look at a SUMIF within a set rolling period (ie 12 months, 4 weeks etc).
There is already a macro in place (definitely not something i've done) to automatically remove any duplicate employee lines from the raw data (report copy tab) and show only one line for each employee in summary tab.

Now i just want to be able to put a formula in for column I in summary tab (working days 12 months) where it will only add the days from report copy tab for each employee if it was in the last 12 months. I've shown what formulas are already being used for totalling working days for example in column E.

Can anyone help me here please to achieve my goal?

THank you in advance
G

 

[RoryA]

Welcome to the forum.

It would be helpful if you could at least post the formulas here as text so we don't have to retype them completely. Which date column is relevant for whether to include the row in the calculation (or do both need to be checked)?

 

[MissG]

Welcome to the forum.

It would be helpful if you could at least post the formulas here as text so we don't have to retype them completely. Which date column is relevant for whether to include the row in the calculation (or do both need to be checked)?

Thank you

Formulas are
Working Days (column E- summary sheet) is =IF(B5="","",SUMIF('Report Copy'!$A:$A,'Summary Sheet'!B5,'Report Copy'!$R:$R))
Occasions (Column F- summary sheet) is =IF(B5="","",COUNTIF('Report Copy'!$A:$A,'Summary Sheet'!B5))

Id like to calculate a working days and occasions where the sickness events were only in the last X time (so for example last rolling 12 months, 4 weeks etc) with a working day and occiasion for each time frame (however once i know how to do one i can work out how to do more).
I'd like the data to remain summarised so it shows only one entry per person as per summary sheet, unlike report copy sheet which shows every instance.

Dont worry about the hours columns.
The date to go from (to start the 12 months for example) would be column P ("from") in report copy sheet

Thanks so much

 

[RoryA]

For 12 months, you can do something like:

=IF(B5="","",SUMIFS('Report Copy'!$R:$R,'Report Copy'!$A:$A,B5,'Report Copy'!$P:$P,">="&EDATE(TODAY(),-12)))

For 4 weeks you can use TODAY()-28 in place of the EDATE(TODAY(),-12)