SUMIFS with Multiple Criteria

I have worked up SUMIFS that sums the Final COS for each of main Account Managers individually and for multiple codes in a contracted column that equate to a loss (this worked out fine):

=SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "Doe", Contracted:Contracted, "not won") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "Doe", Contracted:Contracted, "no go") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "Doe", Contracted:Contracted, "client canceled")

Now what I am trying to do is to calculate the same total for our sub-account managers into one lump sum. I have tried many iterations and nothing seems to be catching. The only thing that has worked is the following - but the issue is that doing it this way makes to formula too long and the sheet summary tool is rejecting it:

=SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "johnson", Contracted:Contracted, "not won") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "johnson", Contracted:Contracted, "no go") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "johnson", Contracted:Contracted, "client canceled") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "fowler", Contracted:Contracted, "not won") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "fowler", Contracted:Contracted, "no go") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "fowler", Contracted:Contracted, "client canceled") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "vern", Contracted:Contracted, "not won") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "vern", Contracted:Contracted, "no go") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "vern", Contracted:Contracted, "client canceled")......

I have tried this (<> to each of our main account managers), which is the only other attempt that has been parsable, but this approach is summing each person multiple times and I am getting a giant and inaccurate total:

=SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], <> "jones", Contracted:Contracted, "not won") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], <> "jones", Contracted:Contracted, "no go") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], <> "jones", Contracted:Contracted, "client canceled") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], <> "doe", Contracted:Contracted, "not won") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], <> "doe", Contracted:Contracted, "no go") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], <> "doe", Contracted:Contracted, "client canceled")

How can I simplify the formula and still get the outcome that I'm looking for? I think the solution is to get all of the main Account Managers into one single statement, but I can't make it work. I have tried using OR, <>, AND, different variations, parentheses placement, etc... HELP!

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

AThalmann

Thanks, Mark! For some reason, I cannot figure out how "@cell" works in formulas. I spoke to some colleagues who also use the sheet and our preference was to try not to add any additional columns to the sheet.

What I ended up doing was going with the following approach that I described in the original post, ( =SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], <> "jones", Contracted:Contracted, "not won") + SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], <> "jones", Contracted:Contracted, "no go") + etc., etc.), but tweaked it so that criteria were in a single formula (for each 'Contracted' condition anyway), rather than repeating the same formula multiple times. See below:

=SUMIFS([Final COS]:[Final COS], Contracted:Contracted, "no go", [Account Manager]:[Account Manager], <>"jones", [Account Manager]:[Account Manager], <>"gellar", [Account Manager]:[Account Manager], <>"doe", [Account Manager]:[Account Manager], <>"michaels", [Account Manager]:[Account Manager], <>"phillips", [Account Manager]:[Account Manager], <>"thomas", [Account Manager]:[Account Manager], <>"hutchins", [Account Manager]:[Account Manager], <>"davis") + SUMIFS([Final COS]:[Final COS], Contracted:Contracted, "client canceled", [Account Manager]:[Account Manager], <>"jones", [Account Manager]:[Account Manager], <>"gellar", [Account Manager]:[Account Manager], <>"doe", [Account Manager]:[Account Manager], <>"michaels", [Account Manager]:[Account Manager], <>"phillips", [Account Manager]:[Account Manager], <>"thomas", [Account Manager]:[Account Manager], <>"hutchins", [Account Manager]:[Account Manager], <>"davis") + SUMIFS([Final COS]:[Final COS], Contracted:Contracted, "not won", [Account Manager]:[Account Manager], <>"jones", [Account Manager]:[Account Manager], <>"gellar", [Account Manager]:[Account Manager], <>"doe", [Account Manager]:[Account Manager], <>"michaels", [Account Manager]:[Account Manager], <>"phillips", [Account Manager]:[Account Manager], <>"thomas", [Account Manager]:[Account Manager], <>"hutchins", [Account Manager]:[Account Manager], <>"davis")

This worked for me and was not too long for Smartsheet! I'm not sure why I didn't set it up this way initially, but I blame it on formula fatigue.

Thanks again for your help. I'm sure that whenever I figure out how to incorporate @cell it will be a timesaver for me!

All comments

Mark Cronk

Hi @AThalmann ,

You can shorten your main formula down to:

=SUMIFS([Final COS]:[Final COS], [Account Manager]:[Account Manager], "Doe", Contracted:Contracted, OR(@Cell="not won", @cell="no go", @cell="client canceled"))

Rather than input each person's name, I recommend you add a helper text/number column [Tier]. For main account manager make that row ="main", for sub ="sub".

Then add [tier]:[tier], "main", or Tier:Tier,"Sub", to your SUMIFS formula as a new range and criteria. If you want both you can add tier:tier, OR(@cell="main", @cell/"sub"),

Work?

Mark

AThalmann

This worked for me and was not too long for Smartsheet! I'm not sure why I didn't set it up this way initially, but I blame it on formula fatigue.

Thanks again for your help. I'm sure that whenever I figure out how to incorporate @cell it will be a timesaver for me!

Mark Cronk

Excellent. Glad you found a solution. Thank you for contributing to the Community. Please accept an answer on you post to close out the discussion. Be Well,

Mark

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.