@Vane<\/a> <\/p>If you only want to add those if the Status is Approved, you'd use this formula:<\/p>
=SUMIFS([Approved Change Order Amount.]:[Approved Change Order Amount.], Category:Category, CONTAINS(\"Regulatory Submissions\", @cell), Status:Status, \"Approved\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-21 21:33:13","updateUser":{"userID":154015,"name":"Vane","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Vane","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-21T22:08:16+00:00","banned":0,"punished":0,"private":true,"label":"✭"}}},"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":96887,"type":"question","name":"Help with COUNTIF","excerpt":"Currently I am counting the number of rows assigned to a particular person using a separate sheet. Currently it counts every row based on the value of the adjacent column. The current entry in the cell that displays the count is =COUNTIF({Sheet - Request Tracker Range 9}, Category26). I need to be able to count the number…","categoryID":322,"dateInserted":"2022-10-21T17:48:50+00:00","dateUpdated":null,"dateLastComment":"2022-10-21T17:59:27+00:00","insertUserID":154002,"insertUser":{"userID":154002,"name":"SmartSheet Newbie","url":"https:\/\/community.smartsheet.com\/profile\/SmartSheet%20Newbie","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu1VcnmvyGv6lWBPJmcBlo2-f4c9qfr0HlrcGUdo=s96-c","dateLastActive":"2022-10-21T18:00:34+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":"2022-10-21T17:57:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":12,"score":null,"hot":3332750297,"url":"https:\/\/community.smartsheet.com\/discussion\/96887\/help-with-countif","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96887\/help-with-countif","format":"Rich","lastPost":{"discussionID":96887,"commentID":348294,"name":"Re: Help with COUNTIF","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/348294#Comment_348294","dateInserted":"2022-10-21T17:59:27+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":"2022-10-21T17:57:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-21T18:04:48+00:00","dateAnswered":"2022-10-21T17:59:27+00:00","acceptedAnswers":[{"commentID":348294,"body":"
You would switch over to a COUNTIFS to include multiple range\/criteria sets, include the new range, and then include the new criteria.<\/p>
=COUNTIFS<\/strong>({Sheet - Request Tracker Range 9}, Category26, {New Range}, AND(@cell <> \"Closed\", @cell <> \"Cancelled\")<\/strong>)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-21 18:04:48","updateUser":{"userID":154002,"name":"SmartSheet Newbie","url":"https:\/\/community.smartsheet.com\/profile\/SmartSheet%20Newbie","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu1VcnmvyGv6lWBPJmcBlo2-f4c9qfr0HlrcGUdo=s96-c","dateLastActive":"2022-10-21T18:00:34+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":96877,"type":"question","name":"Cross Reference","excerpt":"Can you have some rows reference one column in a sheet and other rows reference a different column in the same sheet? It seems like I am getting a message that when I reference a column all 1000+ cells are changed to that reference, but I have 26 rows I want to point to a different column. Do I have to relocate them to…","categoryID":322,"dateInserted":"2022-10-21T15:21:09+00:00","dateUpdated":null,"dateLastComment":"2022-10-21T18:04:50+00:00","insertUserID":141122,"insertUser":{"userID":141122,"name":"Amy Swan","url":"https:\/\/community.smartsheet.com\/profile\/Amy%20Swan","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m4nQn5jNYzw!!MnaATF4cmro","dateLastActive":"2022-10-21T22:06:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":141122,"lastUser":{"userID":141122,"name":"Amy Swan","url":"https:\/\/community.smartsheet.com\/profile\/Amy%20Swan","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m4nQn5jNYzw!!MnaATF4cmro","dateLastActive":"2022-10-21T22:06:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":29,"score":null,"hot":3332743559,"url":"https:\/\/community.smartsheet.com\/discussion\/96877\/cross-reference","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96877\/cross-reference","format":"Rich","lastPost":{"discussionID":96877,"commentID":348298,"name":"Re: Cross Reference","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/348298#Comment_348298","dateInserted":"2022-10-21T18:04:50+00:00","insertUserID":141122,"insertUser":{"userID":141122,"name":"Amy Swan","url":"https:\/\/community.smartsheet.com\/profile\/Amy%20Swan","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m4nQn5jNYzw!!MnaATF4cmro","dateLastActive":"2022-10-21T22:06:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-21T18:05:00+00:00","dateAnswered":"2022-10-21T17:52:54+00:00","acceptedAnswers":[{"commentID":348289,"body":"Ok. So it sounds like you are clicking on \"Edit Reference\". Instead... Highlight the entire reference so that the cross sheet link in the formula helper box reads \"Reference another Sheet\". Then try selecting the new column from your source sheet.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-21 18:05:00","updateUser":{"userID":141122,"name":"Amy Swan","url":"https:\/\/community.smartsheet.com\/profile\/Amy%20Swan","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m4nQn5jNYzw!!MnaATF4cmro","dateLastActive":"2022-10-21T22:06:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
