SUMIFS with Multiple Criteria

AThalmann

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!

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!

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

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!

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