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?

 

[Toadstool]

Hi Queysoft,

You could just brute force some SUMIFS

queysoft.xlsx
	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	20-Jul-20	30-Jul-20	10	9	20-May-21	30-May-21	10	7	20-Jul-20	30-Jul-20	10	9	01-Jan-19	02-Jan-19	1	2	01-Jan-19	02-Jan-19	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))

 

[queysoft]

Excellent - I did not have the knowledge to do this.......Now, however, you have got me thinking what else can be done that I thought was not possible!!

All these years I have just sat and tried to Google "how to in Excel" - and today I thought why not ask around - and I am super happy!!!!

Thank you very much for your time taken to reply. It was very kind.......I'm off to think of more stuff!!!!

 

[Toadstool]

Excellent - I did not have the knowledge to do this.......Now, however, you have got me thinking what else can be done that I thought was not possible!!
All these years I have just sat and tried to Google "how to in Excel" - and today I thought why not ask around - and I am super happy!!!!
Thank you very much for your time taken to reply. It was very kind.......I'm off to think of more stuff!!!!

You're welcome!

 

[queysoft]

HI again. Ok, so I have messed about with the formula you provided and it works fine for me needs. I then decided i would like to do the following - I tried to mess with the same formula structure but to no avail. Sometimes I got errors, other times Excel was happy but didn't give me the results i expected......So....

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 is GREATER than 6 months from a date value specified in a different cell (Say, Z1).

So, I have all my leave dates in a row ranging from say Jan 1 2021 to Dec 1 2021 but instead of summing the days up as before, this time i want to be able to enter a date in a given cell (in this example Z1) and then have the formula calculate the number of days 6 months after that only. So if i enter March 1, then the formula only looks at dates between March 1 and September 1.

Does that make sense? I tried to do it with Edate and then refence Z1+6 but to no avail.......

 

[Toadstool]

The formula I provided checks that both dates (start and end) are within 12 months. When you say "date of leave is GREATER than 6 months from a date value specified in a different cell (Say, Z1)" do you mean the same, i.e. both dates must be greater than 6 months from date in Z1?
...and what about dates after the specified date? Should they be counted or ignored?

 

[Toadstool]

If both start and end must be with 6 months of Z1 date and dates newer than Z1 should be counted then it's a straightforward change...
...but I suspect you may want only days within the range counted, e.g. if start=1 Jan 2020 and end=20 Jan 2020 with Z1=10 Jan 2020 then "Working Days" would be 14 (unless Public Holidays are included) so it would add 14, but I suspect you may want to calculate the Working Days off as 8.

queysoft.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
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 6 Months Working Days Off since 01-Jan-20					01-Jan-20
2	20-Jul-20	30-Jul-20	10	9	20-May-21	30-May-21	10	7	20-Jul-20	30-Jul-20	10	9	01-Jan-19	02-Jan-19	1	2	01-Jan-19	02-Jan-19	1	2	25
Sheet1 (2)

Cell Formulas
Range	Formula
U1	U1	="Last 6 Months Working Days Off since "&TEXT($Z$1,"dd-mmm-yy")
U2	U2	=SUMIFS(D2,A2,">="&EDATE($Z$1,-6),B2,">="&EDATE($Z$1,-6))+SUMIFS(H2,E2,">="&EDATE($Z$1,-6),F2,">="&EDATE($Z$1,-6))+SUMIFS(L2,I2,">="&EDATE($Z$1,-6),J2,">="&EDATE($Z$1,-6))+SUMIFS(P2,M2,">="&EDATE($Z$1,-6),N2,">="&EDATE($Z$1,-6))+SUMIFS(T2,Q2,">="&EDATE($Z$1,-6),R2,">="&EDATE($Z$1,-6))

 

[queysoft]

Ah yes - what I mean is for the 2nd formula, we only need to look at a 6 month range but instead of the 6 month range being from Today() it needs to be from the date in cell Z1. So i tried to switch out the reference in the original formula from Today() to the value in cell Z1 - but it didn't want to know.......

 

[queysoft]

Just read this new reply - let me tinker with this and have a look see.......Thank you - I was sort of close but i think i got my quotes incorrect.......

 

[queysoft]

Great news - all sorted and works. Thank you again for the advice.......... Have a great weekend ahead. Mark.