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
-
AThalmann ✭✭
谢谢,马克!出于某些原因,我不能算出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
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
AThalmann ✭✭
谢谢,马克!出于某些原因,我不能算出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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/74199/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
