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

queysoft

New Member
Joined
Aug 3, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Attachments

  • help_1.JPG
    help_1.JPG
    42.5 KB · Views: 12

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,155
Office Version
  1. 2016
Platform
  1. Windows
Hi Queysoft,

You could just brute force some SUMIFS

queysoft.xlsx
ABCDEFGHIJKLMNOPQRSTU
1StartEndDaysWorking DaysStartEndDaysWorking DaysStartEndDaysWorking DaysStartEndDaysWorking DaysStartEndDaysWorking DaysLast 12 Months Working Days Off
220-Jul-2030-Jul-2010920-May-2130-May-2110720-Jul-2030-Jul-2010901-Jan-1902-Jan-191201-Jan-1902-Jan-19127
Sheet1
Cell Formulas
RangeFormula
U2U2=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))
 
Solution

queysoft

New Member
Joined
Aug 3, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
2,155
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 3, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 5, 2018
Messages
2,155
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
2,155
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1StartEndDaysWorking DaysStartEndDaysWorking DaysStartEndDaysWorking DaysStartEndDaysWorking DaysStartEndDaysWorking DaysLast 6 Months Working Days Off since 01-Jan-2001-Jan-20
220-Jul-2030-Jul-2010920-May-2130-May-2110720-Jul-2030-Jul-2010901-Jan-1902-Jan-191201-Jan-1902-Jan-191225
Sheet1 (2)
Cell Formulas
RangeFormula
U1U1="Last 6 Months Working Days Off since "&TEXT($Z$1,"dd-mmm-yy")
U2U2=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

New Member
Joined
Aug 3, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 3, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 3, 2021
Messages
23
Office Version
  1. 2016
Platform
  1. Windows
Great news - all sorted and works. Thank you again for the advice.......... Have a great weekend ahead. Mark.
 

Forum statistics

Threads
1,147,518
Messages
5,741,638
Members
423,675
Latest member
Dea21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top