SUMIFS with an either or condition

Jesse Shockley

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

Jesse Shockley

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.

Samuel Mueller

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

Jesse Shockley

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?

Samuel Mueller

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}.....)

Jesse Shockley

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.

Samuel Mueller

Oh yeah that makes more sense! Glad you got it working.