Formula
I am new ti Smartsheet and formulas.
This is the formula I have, and it is not working, and I don't know what I have done wrong.
=COUNTIFS({Stage 6}, ="6- Testing", [{Stage 61}, ="6- Opening"])
What I am trying to get is the number of projects that are in phase 6 weather it is opening or testing. Both are values in the same column on the same sheet.
Best Answers
-
Carson Penticuff ✭✭✭✭✭✭Give this a try:
=COUNTIFS({Stage 6}, OR(@cell = "6- Testing", @cell ="6- Opening"))
-
Paul Newcome ✭✭✭✭✭✭Your syntax is a bit off to start. You don't need the square brackets around the second range/criteria set.
=COUNTIFS({Stage 6}, ="6- Testing", {Stage 61}, ="6- Opening")
I also notice that you said they are both in the same column yet you have two different ranges listed in your formula (6 and 61).
Even after correcting the syntax and range issue though, it still will not accomplish what you are wanting because the COUNTIFS implies "and" when looking at multiple range/criteria sets. This means you are technically counting rows where the same cell is equal to both "6- Testing" AND "6- Opening" at the same time which is not possible.
What you really want is to count cells within the same range that are either "6- Testing" OR "6- Opening", and that looks more like:
=COUNTIFS({Range}, OR(@cell = "6- Testing", @cell = "6- Opening"))
Answers
-
Carson Penticuff ✭✭✭✭✭✭
Give this a try:
=COUNTIFS({Stage 6}, OR(@cell = "6- Testing", @cell ="6- Opening"))
-
Paul Newcome ✭✭✭✭✭✭
Your syntax is a bit off to start. You don't need the square brackets around the second range/criteria set.
=COUNTIFS({Stage 6}, ="6- Testing", {Stage 61}, ="6- Opening")
I also notice that you said they are both in the same column yet you have two different ranges listed in your formula (6 and 61).
Even after correcting the syntax and range issue though, it still will not accomplish what you are wanting because the COUNTIFS implies "and" when looking at multiple range/criteria sets. This means you are technically counting rows where the same cell is equal to both "6- Testing" AND "6- Opening" at the same time which is not possible.
What you really want is to count cells within the same range that are either "6- Testing" OR "6- Opening", and that looks more like:
=COUNTIFS({Range}, OR(@cell = "6- Testing", @cell = "6- Opening"))
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/107618/\") + \"% closed rate on ticket status for the month of \" + Month@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":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":54,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+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-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
