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!
Best Answer
-
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!
Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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!
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!