Assistance with COUNTIFS formula...(New to Smartsheet/Formulas)
Hello, I am new to Smartsheet and formulas...I need assistance with a formula (creating a formula sheet for a dashboard).
My formula references another sheet and I want it to count the IN-PROGRESS and UPCOMING Training for an individual trainer. I've created a few formulas that work, but not sure how to create them to count for 2 different training statuses.
INFO:
Sheet Name: Class Tracker
Trainer Name: Aaron
Training Status: IN-PROGRESS, UPCOMING, COMPLETED
FORMULA to count "In-Progress" for Trainer: Aaron..
=COUNTIFS({Class Tracker Range 2}, "Aaron", {Class Tracker Range 1}, "IN-PROGRESS"
Any ideas on how to count both In-Progress and Upcoming?? So far I keep getting #UNPARSEABLE
Thanks!
Best Answer
-
Mark Cronk ✭✭✭✭✭✭Hi@Jani Luna,
Try:
=COUNTIFS({Class Tracker Range 2}, "Aaron", {Class Tracker Range 1}, OR(@cell="IN-PROGRESS" ,@cell= "UPCOMING"))
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Mark Cronk ✭✭✭✭✭✭
Hi@Jani Luna,
Try:
=COUNTIFS({Class Tracker Range 2}, "Aaron", {Class Tracker Range 1}, OR(@cell="IN-PROGRESS" ,@cell= "UPCOMING"))
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Jani Luna ✭✭
Yes!@Mark Cronk- Thank you!
-
Mark Cronk ✭✭✭✭✭✭
You're welcome. Thank you for contributing to the Community.
Be Well,
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
Try setting the criteria to 0.5 instead of 50 as you have the column set to % - right now the formula may be looking for greater than 5000% which obviously won't be found if you're using 0-100%.<\/p>
Let us know if this works or if another solution is required.<\/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":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":265,"urlcode":"reports","name":"Reports"},{"tagID":335,"urlcode":"sheet-summary","name":"Sheet Summary"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}]},{"discussionID":111988,"type":"question","name":"IF AND Formula Question","excerpt":"I wanted get the below formula running... but get an an #UNPARSEABLE error. =IF AND(Number@row, {Wave B (EMEA) - L4 Mapping}, CONTAINS({Wave B (EMEA) - Country x2}, \"BE\"), \"True\", \"False\") Any help would be much appreciated.","snippet":"I wanted get the below formula running... but get an an #UNPARSEABLE error. =IF AND(Number@row, {Wave B (EMEA) - L4 Mapping}, CONTAINS({Wave B (EMEA) - Country x2}, \"BE\"), \"True\",…","categoryID":322,"dateInserted":"2023-10-20T21:18:29+00:00","dateUpdated":null,"dateLastComment":"2023-10-21T08:53:03+00:00","insertUserID":164249,"insertUser":{"userID":164249,"name":"Ulrich Anderhub 1","url":"https:\/\/community.smartsheet.com\/profile\/Ulrich%20Anderhub%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-21T12:48:10+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164249,"lastUser":{"userID":164249,"name":"Ulrich Anderhub 1","url":"https:\/\/community.smartsheet.com\/profile\/Ulrich%20Anderhub%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-21T12:48:10+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":38,"score":null,"hot":3395716292,"url":"https:\/\/community.smartsheet.com\/discussion\/111988\/if-and-formula-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111988\/if-and-formula-question","format":"Rich","lastPost":{"discussionID":111988,"commentID":401071,"name":"Re: IF AND Formula Question","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401071#Comment_401071","dateInserted":"2023-10-21T08:53:03+00:00","insertUserID":164249,"insertUser":{"userID":164249,"name":"Ulrich Anderhub 1","url":"https:\/\/community.smartsheet.com\/profile\/Ulrich%20Anderhub%201","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-21T12:48:10+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-21T08:53:11+00:00","dateAnswered":"2023-10-20T21:36:57+00:00","acceptedAnswers":[{"commentID":401046,"body":"
