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

Find more posts tagged with

Formulas

Accepted answers

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.

All comments

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

I finally figured out I can use nested IF statements and I solved it with this formula:

Thanks again for your help.

Samuel Mueller

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.