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!