# SumIfs Validation Over Multi Years

✭✭✭✭
edited 01/30/23

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?

• ✭✭✭✭

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)

• ✭✭✭✭✭✭

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.

Ryan Sides

Come Say Hello!

• ✭✭✭✭