Can I create a SUMIF "does not contain" with multiple values?
Trying to add up the number of people from all cities but, CityA, CityD, CityF, etc in the same argument. Is there a simple SUMIF formula i could use? Currently I have been using:
=SUMIF([City]1:[City]51, <>"NYC", [# People]1:[# People]51)
but i want Smartsheet to exclude multiple cities.
I've tried a few options but felt the most hopeful with the following.. but they didnt work:
=SUMIF([City]1:[City]51, <>"NYC", <>"LAX", [# People]1:[# People]51)
=SUMIF([City]1:[City]51, <>"NYC",OR[City]1:[City]51, <>"LAX", [# People]1:[# People]51)
=SUMIF([City]1:[City]51, <>"NYC",AND[City]1:[City]51, <>"LAX", [# People]1:[# People]51)
this is likely super simple - any ideas?
Answers
-
Paul Newcome ✭✭✭✭✭✭Try something like this...
=SUMIFS([# People]1:[# People]51, City1:City51,AND(@cell <> "LAX", @cell <> "NYC"))
-
Thanks Andrew. it got me closer! :)
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
<\/p>
Generally speaking, the has function is more designed to work with finding a single text string in a range of multi-select cells. In this case you are working backwards. You are wanting to find the options within a single multi-select cell in a series of text string cells. Flipping the search value and range around backwards should give you backwards functionality.<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":109112,"type":"question","name":"Cannot Figure Logic or Syntax for Nested IF, AND, and OR formula","excerpt":"I have been unable, after 4 straight days of trying, to figure out this formula. I am a novice with Smartsheet. I need a Finding Overdue column (checkbox type) to report if a finding is overdue. Closed findings are automatically not overdue. Open findings have a deadline; however, the deadline may be extended, in which…","snippet":"I have been unable, after 4 straight days of trying, to figure out this formula. I am a novice with Smartsheet. I need a Finding Overdue column (checkbox type) to report if a…","categoryID":322,"dateInserted":"2023-08-17T17:56:53+00:00","dateUpdated":null,"dateLastComment":"2023-08-17T22:11:49+00:00","insertUserID":165284,"insertUser":{"userID":165284,"name":"Rich G","title":"Director of Compliance Initiatives","url":"https:\/\/community.smartsheet.com\/profile\/Rich%20G","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiuNrOI4KDeCiW-Na6OKZMJFiktSQw2flOi0MI5=s96-c","dateLastActive":"2023-08-17T22:11:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":165284,"lastUser":{"userID":165284,"name":"Rich G","title":"Director of Compliance Initiatives","url":"https:\/\/community.smartsheet.com\/profile\/Rich%20G","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiuNrOI4KDeCiW-Na6OKZMJFiktSQw2flOi0MI5=s96-c","dateLastActive":"2023-08-17T22:11:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":24,"score":null,"hot":3384606522,"url":"https:\/\/community.smartsheet.com\/discussion\/109112\/cannot-figure-logic-or-syntax-for-nested-if-and-and-or-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109112\/cannot-figure-logic-or-syntax-for-nested-if-and-and-or-formula","format":"Rich","lastPost":{"discussionID":109112,"commentID":391315,"name":"Re: Cannot Figure Logic or Syntax for Nested IF, AND, and OR formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391315#Comment_391315","dateInserted":"2023-08-17T22:11:49+00:00","insertUserID":165284,"insertUser":{"userID":165284,"name":"Rich G","title":"Director of Compliance Initiatives","url":"https:\/\/community.smartsheet.com\/profile\/Rich%20G","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiuNrOI4KDeCiW-Na6OKZMJFiktSQw2flOi0MI5=s96-c","dateLastActive":"2023-08-17T22:11:44+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/0IRQWFLWJCJC\/untitled-1.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Untitled-1.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-17T22:11:41+00:00","dateAnswered":"2023-08-17T20:03:19+00:00","acceptedAnswers":[{"commentID":391270,"body":"
I don't see anything in your formula referencing the actual revised date. Give this a try:<\/p>
=IF(AND([Finding Status]@row <> \"Compliance Finding is Closed\", [Finding Status]@row <> \"Advisory Recommendation is Closed\", [Finding Status]@row <> \"Advisory Recommendation Not Accepted by Subrecipient\", IF(ISDATE([Revised Compliance Remedy Deadline]@row), [Revised Compliance Remedy Deadline]@row, [Compliance Remedy Deadline]@row)< TODAY()), 1)<\/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":109109,"type":"question","name":"Trouble Setting Up a Workflow Automation Asking for Additional Information","excerpt":"I want to send an individual in a row a request to provide updated information to a sheet using a workflow form once the Send Request is checked. However, when trying to select the \"Send to Contacts in a Cell\" it doesn't give me the option to select a contact field with their email. Also, I want to pre-populate their…","snippet":"I want to send an individual in a row a request to provide updated information to a sheet using a workflow form once the Send Request is checked. However, when trying to select…","categoryID":322,"dateInserted":"2023-08-17T17:06:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-17T20:09:43+00:00","insertUserID":143133,"insertUser":{"userID":143133,"name":"tiernosc","title":"Dr.","url":"https:\/\/community.smartsheet.com\/profile\/tiernosc","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/A4IB9V0NPVJS\/n86WWQNSXCC15.jpg","dateLastActive":"2023-08-17T20:32:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-17T21:38:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3384596764,"url":"https:\/\/community.smartsheet.com\/discussion\/109109\/trouble-setting-up-a-workflow-automation-asking-for-additional-information","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109109\/trouble-setting-up-a-workflow-automation-asking-for-additional-information","format":"Rich","lastPost":{"discussionID":109109,"commentID":391276,"name":"Re: Trouble Setting Up a Workflow Automation Asking for Additional Information","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391276#Comment_391276","dateInserted":"2023-08-17T20:09:43+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-17T21:38:56+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/8KNRVZB43RA6\/screenshot.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-17T19:40:09+00:00","dateAnswered":"2023-08-17T17:54:48+00:00","acceptedAnswers":[{"commentID":391228,"body":"
Make sure that column is set as a contact type column.<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
