SOLVED

# Rolling 365 Calculation Glitch

Copper Contributor

# 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

8 Replies

In D368:

=SUM(B4:B368)

In E368:

=SUM(C4:C368)

Fill down

# Re: Rolling 365 Calculation Glitch

You shall use TODAY(), not TODAY

``=SUMIF(date,">="&TODAY()-365,Col)``

With TODAY condition returns error, thus not met, thus result is zero.

# Re: Rolling 365 Calculation Glitch

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?

# Re: Rolling 365 Calculation Glitch

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!
Seth

# Re: Rolling 365 Calculation Glitch

The formula that I posted will work correctly if column A lists all dates once, without gaps.

# Re: Rolling 365 Calculation Glitch

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.

best response confirmed by sdg33139 (Copper Contributor)
Solution

# Re: Rolling 365 Calculation Glitch

@HansVogelaar THANK YOU for your help.

# Re: Rolling 365 Calculation Glitch

Thank you Sergei for your help. I greatly appreciate it.
1 best response

Accepted Solutions
best response confirmed by sdg33139 (Copper Contributor)
Solution

# Re: Rolling 365 Calculation Glitch

@HansVogelaar THANK YOU for your help.