Forum Discussion
Rolling 365 Calculation Glitch
Hi
I am trying to track days within certain countries for tax purposes using a rolling 365 calculation. I've followed tips from other posts but cannot get the formulas to work. Here are the specifics:
- Column A is DATE going back to January 2016
- Column B is COL for Colombia and has a value of 1.0 if I was in the country and 0 if I was outside the country
- Column C is USA and has a value of 1.0 if I was in the country and 0 if I was outside the country
- Column D is titled Total COL/365 and has a rolling 365 calculation formula that fails
- Column E is Total USA/365 and has a rolling 365 calculation formula that fails
I am placing the first rolling 365 calculation formulas in Columns D & E on January 1, 2017 so as to allow 365 days to have elapsed. I know from using COUNT, that I was in Colombia 95 days in 2016 and 271 days in the USA.
The formulas I am using are:
- =SUMIF(Date,">="&TODAY-365,CoL)
- =SUMIF(Date,">="&TODAY-365,USA)
In an ideal world, I want to copy the formula down and calculate the rolling 365 total for both countries through 2023 as I have the data.
Any thoughts will be greatly appreciated!
Thank you
Seth
HansVogelaar THANK YOU for your help.
- Hi Hans
I greatly appreciate you taking the time to help.
Can it really be this easy for a Rolling 365 calculation? Or does this solution miss something that would be captured using SUMIF?
Thank you!
SethThe formula that I posted will work correctly if column A lists all dates once, without gaps.
You shall use TODAY(), not TODAY
=SUMIF(date,">="&TODAY()-365,Col)With TODAY condition returns error, thus not met, thus result is zero.
Thank you for your help. Unfortunately, the SUMIF formula is not returning the correct values. For example, using the suggested formula =SUMIF(Date,">="&TODAY()-365,CoL) that I placed in the row for January 1, 2017 (to have 365 days of data above this row), the SUMIF formula said that I was in Colombia 88 days. However, using a simple SUM formula returned the correct day count of 95 days. Similarly, for my days in USA, the =SUMIF(Date,">="&TODAY()-365,USA) resulted in 315 days vs. 271 using a simple SUM.
Any thoughts?
I misunderstood. Sum within 365 days back from the date in current row, in D2
=SUMIFS($B$2:$B$1000, $A$2:$A$1000, "<=" & $A2, $A$2:$A$10000, ">=" & ($A2-365) )and drag it down.
Same for the next column.
