Countifs with and

HH_BE1894
HH_BE1894
edited 07/12/23 inFormulas and Functions

I'm working on a metrics sheet and trying to count all the instances where a project is assigned to a particular person (metric@row) within the current year (parent cell) AND ONLY if the project status is New, In Progress or Approval Pending (Metric21, Metric22, and Metric23 in metrics sheet).

My formula before factoring in the project status:

=COUNTIFS({Broth Launch - Responsible}, FIND(Metric@row, @cell) >= 1, {Broth Launch - Year}, PARENT())

How do I edit this formula to return a sum of only the projects where {Broth Launch-Status} equals Metric21, Metric22 or Metric23?

Thanks!

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
\"//www.santa-greenland.com/community/discussion/107492/\"),…","snippet":"I have a perplexing question, that I am sure someone knows and I am missing here. I am working on a sheet to gather data from a form. However, this form will get quite lengthily…","categoryID":322,"dateInserted":"2023-08-23T21:21:49+00:00","dateUpdated":null,"dateLastComment":"2023-08-24T14:04:04+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-08-23T21:50:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-24T14:09:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3385713353,"url":"https:\/\/community.smartsheet.com\/discussion\/109369\/indexing-and-validating","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109369\/indexing-and-validating","format":"Rich","lastPost":{"discussionID":109369,"commentID":392314,"name":"Re: INDEXING and Validating","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392314#Comment_392314","dateInserted":"2023-08-24T14:04:04+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-24T14:09:02+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-08-23T21:50:04+00:00","dateAnswered":"2023-08-23T21:43:36+00:00","acceptedAnswers":[{"commentID":392246,"body":"

@Frank Hammond<\/a> <\/p>

Your formula is close, looks like you just need commas between your criterion range and criterion.<\/p>

=INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 2}, = 9, {Test Sheet 1 Range 3}, = 2023), Index@row)<\/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":109368,"type":"question","name":"CountIFs referencing multiple columns, one of which is multi-select","excerpt":"I am trying to do COUNTIFS with multiple criteria\/columns, referencing another sheet. Essentially, I need a Count of if the items meet the following criteria: Were created in X Month (text column) Were created in X Year (text column) AND a multi-select column contains \"North America\" I have my formula partially down, but…","snippet":"I am trying to do COUNTIFS with multiple criteria\/columns, referencing another sheet. Essentially, I need a Count of if the items meet the following criteria: Were created in X…","categoryID":322,"dateInserted":"2023-08-23T21:05:41+00:00","dateUpdated":"2023-08-23T21:06:24+00:00","dateLastComment":"2023-08-24T13:24:46+00:00","insertUserID":156894,"insertUser":{"userID":156894,"name":"Jessica Z","url":"https:\/\/community.smartsheet.com\/profile\/Jessica%20Z","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bam1G8UgVDg!n-mxX59l_jw!vKSG4USd3sh","dateLastActive":"2023-08-24T13:23:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":156894,"lastUserID":156894,"lastUser":{"userID":156894,"name":"Jessica Z","url":"https:\/\/community.smartsheet.com\/profile\/Jessica%20Z","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bam1G8UgVDg!n-mxX59l_jw!vKSG4USd3sh","dateLastActive":"2023-08-24T13:23:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3385709427,"url":"https:\/\/community.smartsheet.com\/discussion\/109368\/countifs-referencing-multiple-columns-one-of-which-is-multi-select","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109368\/countifs-referencing-multiple-columns-one-of-which-is-multi-select","format":"Rich","lastPost":{"discussionID":109368,"commentID":392307,"name":"Re: CountIFs referencing multiple columns, one of which is multi-select","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392307#Comment_392307","dateInserted":"2023-08-24T13:24:46+00:00","insertUserID":156894,"insertUser":{"userID":156894,"name":"Jessica Z","url":"https:\/\/community.smartsheet.com\/profile\/Jessica%20Z","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bam1G8UgVDg!n-mxX59l_jw!vKSG4USd3sh","dateLastActive":"2023-08-24T13:23:57+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-08-24T13:23:55+00:00","dateAnswered":"2023-08-23T21:10:03+00:00","acceptedAnswers":[{"commentID":392237,"body":"

@Jessica Z<\/a> How were you using the HAS function?<\/p>

Try this:<\/p>

=COUNTIFS({RPA Client Catalog Month}, \"January\", {RPA Client Catalog - Year Bot WL}, \"2023\", {RPA Client Catalog - Region}, has(@cell, \"North America\"))<\/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":109367,"type":"question","name":"Number of weeks countdown from today to start date","excerpt":"Hi! I've searched other threads and haven't found a formula that works for me. I'm trying to create a countdown in weeks for events on a sheet to pull into a dashboard. I want to display the number of weeks from today's date to the event start date. I tried this formula: =WEEKNUMBER(TODAY()) - WEEKNUMBER([Start Date]12)…","snippet":"Hi! I've searched other threads and haven't found a formula that works for me. I'm trying to create a countdown in weeks for events on a sheet to pull into a dashboard. I want to…","categoryID":322,"dateInserted":"2023-08-23T20:21:42+00:00","dateUpdated":null,"dateLastComment":"2023-08-23T21:20:16+00:00","insertUserID":165582,"insertUser":{"userID":165582,"name":"Brittany Sanders","url":"https:\/\/community.smartsheet.com\/profile\/Brittany%20Sanders","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!0ljZv02FeZY!hFFbofG46Xw!7ZO-b0uofYq","dateLastActive":"2023-08-23T21:47:00+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-08-24T14:55:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":35,"score":null,"hot":3385650718,"url":"https:\/\/community.smartsheet.com\/discussion\/109367\/number-of-weeks-countdown-from-today-to-start-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109367\/number-of-weeks-countdown-from-today-to-start-date","format":"Rich","tagIDs":[254,292],"lastPost":{"discussionID":109367,"commentID":392241,"name":"Re: Number of weeks countdown from today to start date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392241#Comment_392241","dateInserted":"2023-08-23T21:20:16+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-08-24T14:55:11+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-08-23T21:47:20+00:00","dateAnswered":"2023-08-23T20:36:16+00:00","acceptedAnswers":[{"commentID":392228,"body":"

Try something like this:<\/p>

=(TODAY() - [Start Date]@row) \/ 7<\/p>"},{"commentID":392233,"body":"

Try flipping the first part around so that you are subtracting today from the start date.<\/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":292,"urlcode":"dashboards","name":"Dashboards"}]}],"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