SumIfs Validation Over Multi Years

Nick Allgauer
Nick Allgauer ✭✭✭✭
edited 01/30/23 in Formulas and Functions

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

  • Nick Allgauer
    Nick Allgauer ✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!