SumIfs Validation Over Multi Years
Hello,
I have a complex sumifs statement that I'm struggling with.
=SUMIFS({Total}, {Site Abbrev.}, $[Site Abbrev.]@row, {Day}, <=$[Day #]$1, {Day}, >=$[Day #]$2, {Year}, <=$Year$1, {Year}, >=$Year$2)
I need to sum the total column of the referenced sheet but only if the site abbreviation matches, the yearday is between Day 1 & Day 2, and Year is between Year 1 & Year 2. Day 2 is another formula that simply says Day 1 minus 6, so I'm trying to sum the last 7 days of data within a range.
Where it gets wonky is when I cross years (hence the need for the condition). If I enter 01/03/2023 in Day 1, the yearday will be 3. Day 2 will show as 12/28/2022 and yearday 362. The formula doesn't have an error, but it does calculate the sum as 0 which is incorrect.
Any ideas on how to validate the yearday and the year in this formula to ensure I'm getting the right sum?
Best Answer

Not quite, but I think you helped point out that I was making this too complicated. I simplified into this:
=SUMIFS({Total}, {Site Abbrev.}, $[Site Abbrev.]@row, {Date}, >=$[Date Range]$2, {Date}, <=$[Date Range]$1)
Answers

Not quite, but I think you helped point out that I was making this too complicated. I simplified into this:
=SUMIFS({Total}, {Site Abbrev.}, $[Site Abbrev.]@row, {Date}, >=$[Date Range]$2, {Date}, <=$[Date Range]$1)
Help Article Resources
Categories
Check out the Formula Handbook template!