Help With COUNTIF Formula
Answers
-
Allison Bishop ✭✭✭✭
Hi,
=COUNTIF([Assigned Work]:[Assigned Worker]”contactname")
This formula is great. However, how would you incorporate a due date? I only want to count the items that are current (meaning not past the due date) for the assigned worker.
Thank you,
Allison
-
Genevieve P. Employee AdminHi Allison,
Instead of COUNTIF (singular), try COUNTIFS (with an S, plural), listing out each range and then criteria:
=COUNTIFS([Assigned Worker]:[Assigned Worker], "Contact Name", [Date Column]:[Date Column], >=TODAY())
This will only count items that are assigned to a specific person and also either have a date of Today or in the Future. Is that what you were looking for? You can take out the = sign in front of Today if you want to only look at future dates.
Here are some Help Center articles I used:COUNTIFS function/TODAY function/Formula tips and tricks
Cheers!
Genevieve
Help Article Resources
Categories
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>
COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell = Agent@row, Date:Date, @cell = Date@row))<\/p>"},{"commentID":385417,"body":"
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":46,"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":27,"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>
<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/NZO376RWLNQF\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
