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
Check out the Formula Handbook template!