Adding only numbers in a column that contain certain text in another column
I need to add only the numbers in the SUBTOTAL VACANT USEABLE AREA column where the SUITE NAME column has the text "VACANT". Some of the suite names have text in addition to "VACANT" . These numbers need to be included in the sum as well, but I've not been able to make this happen- the formula I'm using will only include the cells that only have "VACANT". I think the command "FIND" may be what I need, but I'm not familiar with how to use this command. Whatever function I use, it will be repeated for multiple floors of a building.
Thank you for your help!
Best Answer
-
Yes- It worked! Thank you so much!
Answers
-
L_123 ✭✭✭✭✭✭
=SUMIF([suite name]:[suite name], CONTAINS("Vacant", @cell), [subtotal vacant useable area]:[subtotal vacant useable area])
-
Yes- It worked! Thank you so much!
Help Article Resources
Categories
=LEFT([Manufacturer | Model]@row, FIND(\"╏\", [Manufacturer | Model]@row) - 2)<\/p>
In the formula you posted, you are subtracting the location of your delimiter character (10) from the total length of the cell value (31). The result is 21, so it gives you the first 21 characters from the left. Instead, we just find the location of the delimiter and subtract two to account for the character itself and the space before it.<\/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":111704,"type":"question","name":"Custom Auto Numbering Function","excerpt":"Hi! New Smartsheet user here, appreciate any help and guidance. I'm looking to create a formula to build a custom auto-number function. I'm including a screenshot for reference. I'd like to automate the Project ID column. Where the project ID is YEAR - CLIENT - CLIENT PROJECT NUMBER. Is there a formula out there that would…","snippet":"Hi! New Smartsheet user here, appreciate any help and guidance. I'm looking to create a formula to build a custom auto-number function. I'm including a screenshot for reference.…","categoryID":322,"dateInserted":"2023-10-16T13:36:22+00:00","dateUpdated":null,"dateLastComment":"2023-10-16T19:46:06+00:00","insertUserID":168537,"insertUser":{"userID":168537,"name":"BrendonP","url":"https:\/\/community.smartsheet.com\/profile\/BrendonP","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ACg8ocLStApCIdkhEJ3Z75kH4jR1mcljjo-xOMtR5CGeB5QEO5M=s96-c","dateLastActive":"2023-10-16T19:45:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168537,"lastUser":{"userID":168537,"name":"BrendonP","url":"https:\/\/community.smartsheet.com\/profile\/BrendonP","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ACg8ocLStApCIdkhEJ3Z75kH4jR1mcljjo-xOMtR5CGeB5QEO5M=s96-c","dateLastActive":"2023-10-16T19:45:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":21,"score":null,"hot":3394951348,"url":"https:\/\/community.smartsheet.com\/discussion\/111704\/custom-auto-numbering-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111704\/custom-auto-numbering-function","format":"Rich","tagIDs":[254,334],"lastPost":{"discussionID":111704,"commentID":400224,"name":"Re: Custom Auto Numbering Function","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400224#Comment_400224","dateInserted":"2023-10-16T19:46:06+00:00","insertUserID":168537,"insertUser":{"userID":168537,"name":"BrendonP","url":"https:\/\/community.smartsheet.com\/profile\/BrendonP","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ACg8ocLStApCIdkhEJ3Z75kH4jR1mcljjo-xOMtR5CGeB5QEO5M=s96-c","dateLastActive":"2023-10-16T19:45:52+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\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTQWZ62I6QMN\/screenshot-2023-10-16-at-9-31-09-am.png"},"alt":"Screenshot 2023-10-16 at 9.31.09 AM.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-16T19:46:14+00:00","dateAnswered":"2023-10-16T17:44:23+00:00","acceptedAnswers":[{"commentID":400184,"body":"
You would just need to rearrange the sections.<\/p>
<\/p>
You can see in the formula there are two instances of<\/p>
+ \"-\" +<\/p>
<\/p>
Those are your delimiters. Everything to the left of the first one is what outputs the \"R\". Everything to the right of the second one outputs the \"20\", and everything in between outputs the count.<\/p>
<\/p>
So right now the formula is going<\/p>
left + \"-\" + middle + \"-\" + right<\/p>
<\/p>
You want<\/p>
right + \"-\" + left + \"-\" + middle<\/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"},{"tagID":334,"urlcode":"automations","name":"Automations"}]},{"discussionID":111688,"type":"question","name":"Notifications - Will duplicate e-mails be sent?","excerpt":"I am setting up quite a few notifications on a sheet. Alerts need to go out to 'Contacts in a Cell' as well as 'specific people'. In some cases, the same person could fall in more than one notification contact categories and could also be in the 'Specific People' list. For example: Alert Contacts in a cell could be the…","snippet":"I am setting up quite a few notifications on a sheet. Alerts need to go out to 'Contacts in a Cell' as well as 'specific people'. In some cases, the same person could fall in more…","categoryID":322,"dateInserted":"2023-10-15T20:46:10+00:00","dateUpdated":null,"dateLastComment":"2023-10-16T16:54:56+00:00","insertUserID":168164,"insertUser":{"userID":168164,"name":"Marianne Wilshynsky","title":"Project Executive","url":"https:\/\/community.smartsheet.com\/profile\/Marianne%20Wilshynsky","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-16T17:24:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168164,"lastUser":{"userID":168164,"name":"Marianne Wilshynsky","title":"Project Executive","url":"https:\/\/community.smartsheet.com\/profile\/Marianne%20Wilshynsky","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-16T17:24:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3394879266,"url":"https:\/\/community.smartsheet.com\/discussion\/111688\/notifications-will-duplicate-e-mails-be-sent","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111688\/notifications-will-duplicate-e-mails-be-sent","format":"Rich","lastPost":{"discussionID":111688,"commentID":400169,"name":"Re: Notifications - Will duplicate e-mails be sent?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400169#Comment_400169","dateInserted":"2023-10-16T16:54:56+00:00","insertUserID":168164,"insertUser":{"userID":168164,"name":"Marianne Wilshynsky","title":"Project Executive","url":"https:\/\/community.smartsheet.com\/profile\/Marianne%20Wilshynsky","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-16T17:24: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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-16T16:55:28+00:00","dateAnswered":"2023-10-16T16:30:41+00:00","acceptedAnswers":[{"commentID":400162,"body":"
