Count Active assignments by individual

Amy A
Amy A ✭
edited 09/30/23 inFormulas and Functions

=COUNTIFS([Assigned To]:[Assigned To], ("Name"))AND [Request Status];[Request Status], is one of "In Progress", "Submitted", "Completed")


My countifs statement works by name but I want to further refine this by [Request Status];[Request Status], is one of "In Progress", "Submitted", "Completed")

· Share on Facebook Share on Twitter

Best Answer

  • Amy A
    Amy A ✭
    Answer ✓

    Resolved - =COUNTIFS([Request Status]:[Request Status], OR(@cell = "In Progress", @cell = "Submitted", @cell = "Closed"), [Assigned To]:[Assigned To], "Name")

    · Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
@Dakota Haeffner<\/a> It seems that Week 1 QA is triggering it due to being blank for that particular department. <\/p>

Can you split your current set up into 2 separate conditions and test it, but make \"Is not one Of\" your first condition so that the worklfow first filters out these departments and then add that Week 1 QA should be blank?<\/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":334,"urlcode":"automations","name":"Automations"}]},{"discussionID":111456,"type":"question","name":"Vacation Days Formula - calculating half days","excerpt":"Hi, I am trying to incorporate 0.5 days of annual leave into my vacation tracker. I have the forumla =NETWORKDAYS([Start Date]@row, [End Date]@row, {Bank Holiday Lookp}) to calculate overall number of days out (minus public holidays). From this total I then want to include any 0.5 days from this when 'half day requested'…","snippet":"Hi, I am trying to incorporate 0.5 days of annual leave into my vacation tracker. I have the forumla =NETWORKDAYS([Start Date]@row, [End Date]@row, {Bank Holiday Lookp}) to…","categoryID":322,"dateInserted":"2023-10-10T14:45:29+00:00","dateUpdated":"2023-10-10T23:19:23+00:00","dateLastComment":"2023-10-10T15:32:23+00:00","insertUserID":168251,"insertUser":{"userID":168251,"name":"Linda Wills","title":"Ms","url":"https:\/\/community.smartsheet.com\/profile\/Linda%20Wills","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-10T15:27:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":166028,"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-10-10T18:09:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":21,"score":null,"hot":3393902872,"url":"https:\/\/community.smartsheet.com\/discussion\/111456\/vacation-days-formula-calculating-half-days","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111456\/vacation-days-formula-calculating-half-days","format":"Rich","tagIDs":[210,219,254,281],"lastPost":{"discussionID":111456,"commentID":399296,"name":"Re: Vacation Days Formula - calculating half days","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/399296#Comment_399296","dateInserted":"2023-10-10T15:32:23+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-10-10T18:09:36+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-10T15:27:13+00:00","dateAnswered":"2023-10-10T15:26:21+00:00","acceptedAnswers":[{"commentID":399286,"body":"

You don't need the _if_true \/ false pieces.<\/p>

=NETWORKDAYS([Start Date]63, [End Date]63) - IF([Half-day]63 = \"Yes\", .5, 0)<\/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":210,"urlcode":"human-resources","name":"Human Resources"},{"tagID":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":281,"urlcode":"support","name":"Support"}]},{"discussionID":111433,"type":"question","name":"MCQ form","excerpt":"I created an MCQ form that I need to evaluate. for example: 1 question, if the correct is answer is C, it can give one mark or zero mark. like this way created 5 questions, 6th column is the result. it needs to sum up the answer. Please help me.","snippet":"I created an MCQ form that I need to evaluate. for example: 1 question, if the correct is answer is C, it can give one mark or zero mark. like this way created 5 questions, 6th…","categoryID":322,"dateInserted":"2023-10-10T09:02:35+00:00","dateUpdated":null,"dateLastComment":"2023-10-10T11:44:55+00:00","insertUserID":168230,"insertUser":{"userID":168230,"name":"kumars23","url":"https:\/\/community.smartsheet.com\/profile\/kumars23","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-10T11:45:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-10T13:32:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":35,"score":null,"hot":3393869850,"url":"https:\/\/community.smartsheet.com\/discussion\/111433\/mcq-form","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111433\/mcq-form","format":"Rich","lastPost":{"discussionID":111433,"commentID":399251,"name":"Re: MCQ form","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/399251#Comment_399251","dateInserted":"2023-10-10T11:44:55+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-10T13:32:33+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-10T11:39:23+00:00","dateAnswered":"2023-10-10T11:03:49+00:00","acceptedAnswers":[{"commentID":399248,"body":"

In this case you would change it to:<\/p>

=IF([1.Question]@row = \"C\",1,0)<\/p>

The [ ] brackets go around your column name that's being referenced.<\/p>"},{"commentID":399251,"body":"

If you had a column for each (1Q, 2Q, etc.) arranged in the way you've started, then you would use either of:<\/p>

=1Q@row + 2Q@row + 3Q@row +4Q@row +5Q@row <\/p>

=SUM([1Q]@row, [2Q]@row, [3Q]@row, [4Q]@row, [5Q]@row)<\/p>

If you want to skip this, then this should work:<\/p>

=IF([1.Question]@row = \"C\", 1, 0) + IF([2.Question]@row = \"B\", 1, 0) + IF([3.Question]@row = \"A\", 1, 0) + IF([4.Question]@row = \"B\", 1, 0) + IF([5.Question]@row = \"B\", 1, 0)<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/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":[]}">

Trending in Formulas and Functions

  • Automation Condition Not working

    Hey all, I set up an automation to request an update if one field is blank and if another field is not a specific list of departments and I got an email this from someone in that department saying they got an email anyways. Can you take a look at the below screenshots and tell me if I'm missing something? Here is the…

  • Vacation Days Formula - calculating half days

    Hi, I am trying to incorporate 0.5 days of annual leave into my vacation tracker. I have the forumla =NETWORKDAYS([Start Date]@row, [End Date]@row, {Bank Holiday Lookp}) to calculate overall number of days out (minus public holidays). From this total I then want to include any 0.5 days from this when 'half day requested'…

  • MCQ form

    I created an MCQ form that I need to evaluate. for example: 1 question, if the correct is answer is C, it can give one mark or zero mark. like this way created 5 questions, 6th column is the result. it needs to sum up the answer. Please help me.