Count values, only if within the past 12 months over several columns

[queysoft]

Hi - First post to this forum as I am just stuck on this...........Its a simple table to show employees leave. I have a list of names down one side and then the same headers repeated and then just populated as people go on leave.

Column A = Date of Leave 1
Column B = Date of Return 1
Column C = Number of actual days away
Column D = Number of working days away

Column E onwards will show the same as A to D but for all the other days away (if there are any). So, for one employee I might have 5 different leave return dates and for another perhaps only 1.

I am trying to construct a formula to show the SUM value of column D plus all the other columns (so H, L......n) only if the date of leave and return falls in the last 12 months.

So - In essence I want a rolling number of days leave every time I open the spreadsheet.

In the attached example, only cells H2 and L2 would be counted.

Any ideas?

 

[Crystalyzer]

What if the Start and End Date of the leave straddles the rolling 12 month date? Like below in column A and B? Column U doesn't include any portion of this leave.

Am I missing something?

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Start	End	Days	Working Days	Start	End	Days	Working Days	Start	End	Days	Working Days	Start	End	Days	Working Days	Start	End	Days	Working Days	Last 12 Months Working Days Off
2	8/1/2020	8/15/2020	10	9	5/20/2021	5/30/2021	10	7	7/20/2020	7/30/2020	10	9	1/1/2019	1/2/2019	1	2	1/1/2019	1/2/2019	1	2	7
Sheet1

Cell Formulas
Range	Formula
U2	U2	=SUMIFS(D2,A2,">="&EDATE(TODAY(),-12),B2,">="&EDATE(TODAY(),-12))+SUMIFS(H2,E2,">="&EDATE(TODAY(),-12),F2,">="&EDATE(TODAY(),-12))+SUMIFS(L2,I2,">="&EDATE(TODAY(),-12),J2,">="&EDATE(TODAY(),-12))+SUMIFS(P2,M2,">="&EDATE(TODAY(),-12),N2,">="&EDATE(TODAY(),-12))+SUMIFS(T2,Q2,">="&EDATE(TODAY(),-12),R2,">="&EDATE(TODAY(),-12))