SumIfs Validation Over Multi Years
![Nick Allgauer](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!