SumIfs Validation Over Multi Years

Nick Allgauer

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?

Nick Allgauer

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)

Ryan Sides

@Nick Allgauer

Would it be appropriate to just subtract the two dates to get the number of days? Or am I missing the issue?

=[Date1]@row - [Date2]@row

When this ^ crosses years, it calculates correctly.

Nick Allgauer

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)