COUNTIFS when value falls between X and Y

Chris Hallo
Chris Hallo ✭✭✭✭
inFormulas and Functions

Hello! So, my summary field for 00-30 days (using Days Open) works fine. I'm running into issues though on 31-60, 61-90 and 91-180...not sure the syntax for the range. Help, please :)!!

image.png


· Share on Facebook Share on Twitter

Best Answers

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 theFormula Handbook template!

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

 Watch video
Hi @KarenTF<\/a>,<\/p>

You can do this with a nested IF statement. As a basic example, if you had a dropdown with options A, B, C and wanted to assign them points values of 1, 2, 3 respectively, you would use the following formula in your points column:<\/p>

=IF([Radio selection]@row = \"A\", 1, IF([Radio selection]@row = \"B\", 2, IF([Radio selection]@row = \"C\", 3)))<\/p>

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

If you have multiple instances of this then you could then use a SUM formula to get the total at the end.<\/p>

Hopefully this points you in the right direction, but if you've more specifics or have any problems\/questions then just post!<\/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":111115,"type":"question","name":"Can Smartsheet automatically push a date on to a Thursday?","excerpt":"Hello. I thought I’d found the answer to this in a forum a while back, but the answer on there didn’t work for me when I tried it – I’d like to know if there’s a code\/formula for Smartsheet to check whether an End Date (publication date) falls on a Thursday and for Smartsheet to automatically move the date along to the…","snippet":"Hello. I thought I’d found the answer to this in a forum a while back, but the answer on there didn’t work for me when I tried it – I’d like to know if there’s a code\/formula for…","categoryID":322,"dateInserted":"2023-10-03T14:07:53+00:00","dateUpdated":null,"dateLastComment":"2023-10-03T15:17:08+00:00","insertUserID":167869,"insertUser":{"userID":167869,"name":"Lee5","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Lee5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-03T21:08:50+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-10-03T21:59:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":29,"score":null,"hot":3392690701,"url":"https:\/\/community.smartsheet.com\/discussion\/111115\/can-smartsheet-automatically-push-a-date-on-to-a-thursday","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111115\/can-smartsheet-automatically-push-a-date-on-to-a-thursday","format":"Rich","tagIDs":[254,334],"lastPost":{"discussionID":111115,"commentID":398125,"name":"Re: Can Smartsheet automatically push a date on to a Thursday?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398125#Comment_398125","dateInserted":"2023-10-03T15:17:08+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-03T21:59: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-10-03T14:58:34+00:00","dateAnswered":"2023-10-03T14:21:50+00:00","acceptedAnswers":[{"commentID":398101,"body":"

@Lee5<\/a> So I can make it so it falls on the current week Thursday, so if it is a Friday it will slide back 1 day.<\/p>

The formula is =[End Date]@row - WEEKDAY([End Date]@row) + 5<\/p>"},{"commentID":398102,"body":"

Give this a try:<\/p>

=[Publication Date]@row + (5 - WEEKDAY([Publication Date]@row)) + IF(WEEKDAY([Publication Date]@row) > 5, 7, 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":254,"urlcode":"formulas","name":"Formulas"},{"tagID":334,"urlcode":"automations","name":"Automations"}]},{"discussionID":111088,"type":"question","name":"Return Ancestor Data by Level Designation","excerpt":"I'm logging multiple years' worth of data into a single sheet that is organized by year, quarter and month at Levels 1, 2 and 3. I believe I need to use the ancestors function along with index (maybe?) but I can't seem to find a formula that works. A snapshot of my table is below. In words, I want my Year column to return…","snippet":"I'm logging multiple years' worth of data into a single sheet that is organized by year, quarter and month at Levels 1, 2 and 3. I believe I need to use the ancestors function…","categoryID":322,"dateInserted":"2023-10-02T22:47:05+00:00","dateUpdated":null,"dateLastComment":"2023-10-04T03:20:50+00:00","insertUserID":163321,"insertUser":{"userID":163321,"name":"HH_BE1894","title":"Brand Manager","url":"https:\/\/community.smartsheet.com\/profile\/HH_BE1894","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-04T03:20:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163321,"lastUser":{"userID":163321,"name":"HH_BE1894","title":"Brand Manager","url":"https:\/\/community.smartsheet.com\/profile\/HH_BE1894","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-04T03:20:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":30,"score":null,"hot":3392678875,"url":"https:\/\/community.smartsheet.com\/discussion\/111088\/return-ancestor-data-by-level-designation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111088\/return-ancestor-data-by-level-designation","format":"Rich","lastPost":{"discussionID":111088,"commentID":398303,"name":"Re: Return Ancestor Data by Level Designation","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398303#Comment_398303","dateInserted":"2023-10-04T03:20:50+00:00","insertUserID":163321,"insertUser":{"userID":163321,"name":"HH_BE1894","title":"Brand Manager","url":"https:\/\/community.smartsheet.com\/profile\/HH_BE1894","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-04T03:20:59+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/TXSKOU3XO3JW\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-04T03:20:56+00:00","dateAnswered":"2023-10-03T20:15:07+00:00","acceptedAnswers":[{"commentID":398254,"body":"

In that case you would use something like this:<\/p>

=IFERROR(INDEX(ANCESTORS(Project@row), 1), Project@row)<\/p>


<\/p>

You just need to change that 1 to a 2 or 3 as needed.<\/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

  • I want to assign a specific value for when a dropdown selection was made for a column.

    If I wanted to assign a specific value for when a dropdown selection was made for a column, what formula should I use. I'm trying to assign a point-value in a separate column that is dependent on the selection made from a dropdown item in another column. For instance, say in a form the user selects a fixed radio button,…

  • Can Smartsheet automatically push a date on to a Thursday?

    Hello. I thought I’d found the answer to this in a forum a while back, but the answer on there didn’t work for me when I tried it – I’d like to know if there’s a code/formula for Smartsheet to check whether an End Date (publication date) falls on a Thursday and for Smartsheet to automatically move the date along to the…

  • Return Ancestor Data by Level Designation

    I'm logging multiple years' worth of data into a single sheet that is organized by year, quarter and month at Levels 1, 2 and 3. I believe I need to use the ancestors function along with index (maybe?) but I can't seem to find a formula that works. A snapshot of my table is below. In words, I want my Year column to return…