[Finish]2), \"At Risk\", \"Normal\")) I want the sheet to check if a task is not yet marked complete (<100 %) AND the current…","categoryID":322,"dateInserted":"2019-04-30T19:14:29+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-01T12:27:11+00:00","insertUserID":87051,"insertUser":{"userID":87051,"name":"UWSOM-EQI","url":"https:\/\/community.smartsheet.com\/profile\/UWSOM-EQI","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-05-09T14:47:50+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":195,"lastUser":{"userID":195,"name":"Shaine Greenwood","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Shaine%20Greenwood","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/198\/nBSBY6VR571R7.jpg","dateLastActive":"2021-08-30T16:03:52+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":373,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/47066\/help-with-if-formula-using-today-formula-and-unparseable-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47066\/help-with-if-formula-using-today-formula-and-unparseable-error","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":249,"urlcode":"Education","name":"Education"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"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}]},{"discussionID":46836,"type":"discussion","name":"Formula calculating monthly totals","excerpt":"I am trying to write a formula that will calculate receipt totals by month (starting this month) Here is the formula i am using, but it is returning an invalid data type =SUMIFS({Joseph Nelums- CRS Expense Reporting Receipt Total}, {Joseph Nelums- CRS Expense Reporting Date}, YEAR(@cell) = YEAR(TODAY()), {Joseph Nelums-…","categoryID":322,"dateInserted":"2019-04-26T13:12:53+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-01T11:54:00+00:00","insertUserID":50951,"insertUser":{"userID":50951,"name":"steve50951","url":"https:\/\/community.smartsheet.com\/profile\/steve50951","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-07-16T18:38:22+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-07-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":811,"score":null,"hot":11,"url":"https:\/\/community.smartsheet.com\/discussion\/46836\/formula-calculating-monthly-totals","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/46836\/formula-calculating-monthly-totals","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-04\/95\/02\/9502060fdb859a3ea708eb70fabb7314.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screen Shot 2019-04-26 at 12.10.04 PM.png"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":47086,"type":"discussion","name":"Circular Reference Help","excerpt":"I know this is a long shot but here goes. I have 2 sheets: a calculation\/capture sheet and a reference date calculation sheet. The first gets information from a web form and does a few on-sheet look-ups and calculations. Two pieces of data are pushed to the second sheet where date calculations occurr and the 3 resulting…","categoryID":322,"dateInserted":"2019-05-01T09:31:51+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-01T10:42:46+00:00","insertUserID":4291,"insertUser":{"userID":4291,"name":"John M Lee","url":"https:\/\/community.smartsheet.com\/profile\/John%20M%20Lee","photoUrl":"https:\/\/lh3.googleusercontent.com\/-XdUIqdMkCWA\/AAAAAAAAAAI\/AAAAAAAAAAA\/4252rscbv5M\/photo.jpg","dateLastActive":"2022-02-11T16:16:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-07-16T22:27:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":256,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/47086\/circular-reference-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47086\/circular-reference-help","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-05\/8b\/c7\/8bc753c6edc1442fafef0574677a9b2d.JPG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Sheet 1 - Capture & Calculation Sheet.JPG"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":46981,"type":"discussion","name":"Trying to get a sum of specific child rows","excerpt":"Hello, My team is tracking hours spent on assigned tasks. We have our SpartSheet set up with: Parent Row : Date, and Total Hours spent for Child Rows Child Row: Team Member, and Hours spent I am looking to see if its possible to get how much time each team member is spending in total ","categoryID":322,"dateInserted":"2019-04-30T12:32:46+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-01T04:56:57+00:00","insertUserID":86966,"insertUser":{"userID":86966,"name":"Elie","url":"https:\/\/community.smartsheet.com\/profile\/Elie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-04-30T12:26:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-07-16T22:27:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":64,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/46981\/trying-to-get-a-sum-of-specific-child-rows","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/46981\/trying-to-get-a-sum-of-specific-child-rows","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}],"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}]},{"discussionID":47056,"type":"discussion","name":"Formula for counting Stars across multiple sheets","excerpt":"I am trying to count the Stars that are checked across multiple sheets. I can’t seem to get a formula to work. Would anyone know?","categoryID":322,"dateInserted":"2019-04-30T17:57:46+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-04-30T22:31:45+00:00","insertUserID":53216,"insertUser":{"userID":53216,"name":"Jessica.Kelly53216","url":"https:\/\/community.smartsheet.com\/profile\/Jessica.Kelly53216","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-07\/65\/fc\/n65fc675742bcf076b9c9c61f15872b88.jpg","dateLastActive":"2019-04-30T17:54:51+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":59916,"lastUser":{"userID":59916,"name":"sean59916","title":"","url":"https:\/\/community.smartsheet.com\/profile\/sean59916","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-08\/b1\/43\/nb14312faa18c300e84f0ec627a78ccc7.jpg","dateLastActive":"2021-02-20T01:58:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":94,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/47056\/formula-for-counting-stars-across-multiple-sheets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47056\/formula-for-counting-stars-across-multiple-sheets","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":47031,"type":"discussion","name":"Formula help counting back 30 days","excerpt":"Hi I am trying to create a formula that count all entries by a certain person within the last 30 days. This is a cross sheet formula and seems to work well. Now comes the however! I have the below formula which does the calculation, however if a date is in the future it still counts it. =COUNTIFS({Monmouthshire Housing…","categoryID":322,"dateInserted":"2019-04-30T15:30:02+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-04-30T17:29:36+00:00","insertUserID":56751,"insertUser":{"userID":56751,"name":"dave.mcpherson56751","url":"https:\/\/community.smartsheet.com\/profile\/dave.mcpherson56751","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-07-04T08:00:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":52616,"lastUser":{"userID":52616,"name":"Nic Larsen","url":"https:\/\/community.smartsheet.com\/profile\/Nic%20Larsen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-07-14T21:23:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":191,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/47031\/formula-help-counting-back-30-days","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47031\/formula-help-counting-back-30-days","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":46971,"type":"discussion","name":"countifs with a date criteria","excerpt":"I am trying to create a countifs formula that would count \"New Quotes\" with a January date while it's referencing another sheet. New Quotes would be in the New Quote\/Repricing column of this spreadsheet and then there's the Proposal date column that is in a MM\/DD\/YY format. =COUNTIFS({CPI Prospects 2019 Range 1}, \"New…","categoryID":322,"dateInserted":"2019-04-30T10:52:01+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-04-30T14:52:19+00:00","insertUserID":86936,"insertUser":{"userID":86936,"name":"drodgers","url":"https:\/\/community.smartsheet.com\/profile\/drodgers","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-11-01T15:10:26+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-07-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":131,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/46971\/countifs-with-a-date-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/46971\/countifs-with-a-date-criteria","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":46811,"type":"discussion","name":"Counts if with multiple conditons","excerpt":"Help! Trying to get a count if line items that do not have a column marked resolved and have a issue type of Reports Missing. =COUNTIFS({Aimbridge Report Issues Range 2}, \"Reports Missing\",{Aimbridge Report Issues Range 2}, 0) I am sure it is simple...but I an not figuring it out and need help! ","categoryID":322,"dateInserted":"2019-04-26T10:18:36+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-04-30T14:09:21+00:00","insertUserID":47706,"insertUser":{"userID":47706,"name":"laurie.sather47706","url":"https:\/\/community.smartsheet.com\/profile\/laurie.sather47706","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jPFPo2S0xvo!xPfOcXit5rw!qbk2ZKTJF_U","dateLastActive":"2021-02-05T18:27:59+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-07-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":72,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/46811\/counts-if-with-multiple-conditons","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/46811\/counts-if-with-multiple-conditons","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]},{"discussionID":46771,"type":"discussion","name":"Countifs using iferror or iserror","excerpt":"Hello, I'm trying to use a COUNTIFS formula to count the number of VLOOKUP errors in another sheet that occur in the past month. From what I can see in other discussions the IFERROR and ISERROR functions are generally used to give a 0 value but I want it to return a value of 1 for each error. This is the formula I've been…","categoryID":322,"dateInserted":"2019-04-25T15:42:23+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-04-30T13:23:17+00:00","insertUserID":74576,"insertUser":{"userID":74576,"name":"Tiffany White","url":"https:\/\/community.smartsheet.com\/profile\/Tiffany%20White","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-04-30T13:20:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":74576,"lastUser":{"userID":74576,"name":"Tiffany White","url":"https:\/\/community.smartsheet.com\/profile\/Tiffany%20White","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-04-30T13:20:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":896,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/46771\/countifs-using-iferror-or-iserror","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/46771\/countifs-using-iferror-or-iserror","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":234,"urlcode":"Health-care","name":"Healthcare"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":281,"urlcode":"Support","name":"Support"}],"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}]},{"discussionID":46731,"type":"discussion","name":"Formula help for my dashboard","excerpt":"Hi all I have a sheet that I use to track recruitment activities within our team. Each role is assigned to a member of the team and has a recruitment status attached to it. I am trying to pull this information into my dashboard to show how many active roles each team member has on the go. However, I only want to select…","categoryID":322,"dateInserted":"2019-04-25T10:07:51+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-04-30T08:24:46+00:00","insertUserID":70451,"insertUser":{"userID":70451,"name":"dcabras","url":"https:\/\/community.smartsheet.com\/profile\/dcabras","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-06-11T08:52:31+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-07-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":12,"countViews":133,"score":null,"hot":12,"url":"https:\/\/community.smartsheet.com\/discussion\/46731\/formula-help-for-my-dashboard","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/46731\/formula-help-for-my-dashboard","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":210,"urlcode":"Human Resources","name":"Human Resources"},{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}],"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">
\"//www.santa-greenland.com/community/categories/formulas-and-functions/\", COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell = Agent@row, Date:Date, @cell = Date@row)) > 3), \"Red\", \"//www.santa-greenland.com/community/categories/formulas-and-functions/\") Once the cell worded red, conditional formatting turned the offending cells red with white text…","snippet":"If anyone is looking for something similar here was my answer and worked 100% =IF(AND(Agent@row <> \"//www.santa-greenland.com/community/categories/formulas-and-functions/\", COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell =…","categoryID":322,"dateInserted":"2023-07-14T18:18:52+00:00","dateUpdated":"2023-07-14T20:01:58+00:00","dateLastComment":"2023-07-14T20:39:19+00:00","insertUserID":163519,"insertUser":{"userID":163519,"name":"Frank Hammond","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Hammond","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-07-15T17:58:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":163519,"lastUserID":163519,"lastUser":{"userID":163519,"name":"Frank Hammond","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Hammond","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-07-15T17:58:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":71,"score":null,"hot":3378731891,"url":"https:\/\/community.smartsheet.com\/discussion\/107635\/duplicate-detection-assitance","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107635\/duplicate-detection-assitance","format":"Rich","lastPost":{"discussionID":107635,"commentID":385431,"name":"Re: duplicate detection assitance","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385431#Comment_385431","dateInserted":"2023-07-14T20:39:19+00:00","insertUserID":163519,"insertUser":{"userID":163519,"name":"Frank Hammond","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Hammond","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-07-15T17:58: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\/AURLV19Y7741\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-14T20:00:15+00:00","dateAnswered":"2023-07-14T19:48:25+00:00","acceptedAnswers":[{"commentID":385406,"body":"
Right. That was to be inserted in place of the COUNTIFS in your existing formula, but I did forget to add the date piece in.<\/p>
Bro! that was it! Thank you, I would not thought to use CountM(Collect for this at all. <\/p>
<\/p>
100% thank you!😁<\/span><\/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":107627,"type":"question","name":"COUNTIFS question, stumped","excerpt":"I'm trying to count how many \"Yes\" is in the column (options are Yes and No). The column name is \"Do you plan to stay overnight May 1st?\" I feel like this is probably simple but I've been stuck on it for 2 weeks. HALP","snippet":"I'm trying to count how many \"Yes\" is in the column (options are Yes and No). The column name is \"Do you plan to stay overnight May 1st?\" I feel like this is probably simple but…","categoryID":322,"dateInserted":"2023-07-14T15:44:42+00:00","dateUpdated":null,"dateLastComment":"2023-07-14T16:13:34+00:00","insertUserID":163501,"insertUser":{"userID":163501,"name":"mwd","url":"https:\/\/community.smartsheet.com\/profile\/mwd","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-14T16:12:38+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-07-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":48,"score":null,"hot":3378703096,"url":"https:\/\/community.smartsheet.com\/discussion\/107627\/countifs-question-stumped","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107627\/countifs-question-stumped","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107627,"commentID":385333,"name":"Re: COUNTIFS question, stumped","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385333#Comment_385333","dateInserted":"2023-07-14T16:13:34+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-07-14T20:36: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-14T16:12:44+00:00","dateAnswered":"2023-07-14T15:54:24+00:00","acceptedAnswers":[{"commentID":385325,"body":"
=COUNTIFS([Do you plan to stay overnight May 1st?]:[Do you plan to stay overnight May 1st?], \"Yes\")<\/p>"},{"commentID":385326,"body":"
Sounds like you just need a basic COUNTIFS.<\/p>
=COUNTIFS([Column Name]:[Column name], @cell = \"Yes\")<\/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":107619,"type":"question","name":"Automation for contact column notication, once month by active, for only their projects.","excerpt":"I want to notify everyone in my \"Project Leader\" column of only their own projects that are active once a month. An Automation like... Run once a month on this day at this time, If project status is active, Notify Project Leader of all rows assigned to them. There doesn't seem to already be a way to group projects by…","snippet":"I want to notify everyone in my \"Project Leader\" column of only their own projects that are active once a month. An Automation like... Run once a month on this day at this time,…","categoryID":322,"dateInserted":"2023-07-14T14:13:22+00:00","dateUpdated":null,"dateLastComment":"2023-07-14T17:20:10+00:00","insertUserID":151431,"insertUser":{"userID":151431,"name":"Geilisa","url":"https:\/\/community.smartsheet.com\/profile\/Geilisa","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZnspmT_we0s!7Ijp-7vLSzs!HbngNM8iINM","dateLastActive":"2023-07-14T17:17:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":151431,"lastUser":{"userID":151431,"name":"Geilisa","url":"https:\/\/community.smartsheet.com\/profile\/Geilisa","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZnspmT_we0s!7Ijp-7vLSzs!HbngNM8iINM","dateLastActive":"2023-07-14T17:17:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":29,"score":null,"hot":3378700412,"url":"https:\/\/community.smartsheet.com\/discussion\/107619\/automation-for-contact-column-notication-once-month-by-active-for-only-their-projects","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107619\/automation-for-contact-column-notication-once-month-by-active-for-only-their-projects","format":"Rich","lastPost":{"discussionID":107619,"commentID":385346,"name":"Re: Automation for contact column notication, once month by active, for only their projects.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385346#Comment_385346","dateInserted":"2023-07-14T17:20:10+00:00","insertUserID":151431,"insertUser":{"userID":151431,"name":"Geilisa","url":"https:\/\/community.smartsheet.com\/profile\/Geilisa","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZnspmT_we0s!7Ijp-7vLSzs!HbngNM8iINM","dateLastActive":"2023-07-14T17:17:23+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-14T17:17:54+00:00","dateAnswered":"2023-07-14T14:23:39+00:00","acceptedAnswers":[{"commentID":385281,"body":"
You would set your trigger to be date based and then select the option for Custom Recurrence to set up the monthly portion.<\/p>
You would use a condition of the Status column being \"Active\" to apply the filter.<\/p>
Then the recipient for the Alert would be \"Send To Contacts In A Cell\" and you would select the Project Leader column which will only send rows meeting the previous condition to the appropriate Project Leader(s). So if I am leader on rows 1, 3, and 5 (assuming they are all active), I will only be alerted to rows 1, 3, and 5.<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/NZO376RWLNQF\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
