SUMIFS with an either or condition
I'm struggling to make this work . . . .
I'm starting with a SUMIFS that works:
=SUMIFS({5_METRICS_SALES LEADS Current Year Goal}, {5_METRICS_SALES LEADS Today}, YEAR(@cell) = YEAR(Today@row), {5_METRICS_SALES LEADS Sales Lead}, VARIABLE@row)
The 5_METRICS_SALES LEADS has two columns I want to sum (Current Year Goal and Next Year Goal), depending on what year is in the Today column. The above formula takes care of summing the Current Year Goal when it's the current year, but I can't figure out to make it sum the Next Year Column when it's YEAR(Today@row + 1)
Any help appreciated
Best Answer

Thanks, Samuel  I don't think I explained my issue very well in my original question. I'm not looking to add the two together, but instead sum one if a certain condition exists, and sum the other if a different condition exists.
I finally figured out I can use nested IF statements and I solved it with this formula:
=IF(YEAR@row = YEAR(Today@row), SUMIFS({5_METRICS_SALES LEADS Current Year Goal}, {5_METRICS_SALES LEADS Sales Lead}, VARIABLE@row), IF(YEAR@row = YEAR(Today@row) + 1, SUMIFS({5_METRICS_SALES LEADS Next Year Goal}, {5_METRICS_SALES LEADS Sales Lead}, VARIABLE@row), ""))
Thanks again for your help.
Answers

Try putting the +1 outside of year. YEAR(Today@row) + 1

Thanks, Samuel  but how do I construct this formula to do the either or? I know if I use an OR statement, it has to reference the same column, so that doesn't appear to work. I don't think I can wrap a SUMIFS in an IF statement. . . or can I?

Separate your sum ifs and add them together... so
=sumifs({5_METRICS_SALES LEADS Current Year Goal}....)+sumifs({5_METRICS_SALES LEADS Next Year Goal}.....)

Thanks, Samuel  I don't think I explained my issue very well in my original question. I'm not looking to add the two together, but instead sum one if a certain condition exists, and sum the other if a different condition exists.
I finally figured out I can use nested IF statements and I solved it with this formula:
=IF(YEAR@row = YEAR(Today@row), SUMIFS({5_METRICS_SALES LEADS Current Year Goal}, {5_METRICS_SALES LEADS Sales Lead}, VARIABLE@row), IF(YEAR@row = YEAR(Today@row) + 1, SUMIFS({5_METRICS_SALES LEADS Next Year Goal}, {5_METRICS_SALES LEADS Sales Lead}, VARIABLE@row), ""))
Thanks again for your help.

Oh yeah that makes more sense! Glad you got it working.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!