Formulas and Functions

Archives for August 2018

Auto populate from one sheet into blank rows on another
Hi All! I searched for this, but couldn't find an answer. Maybe it doesn't exist! I'm trying to set up a new dashboard for sales reps. In one sheet I will have them fill out all of their activities. I was TRYING to get them to fill out all information on one sheet to keep things simple for them. Is there a way for me to…
Counting total specific drop-down option between 2 dates
Hello! The sheet that I am working with is used to track agreements my department works with. Two of the data points (among many) that we capture is the agreement type within a drop-down and the signature date. I would like to capture the total "Amendment"'s from the drop-down list with a signature date that fall in our…
countif help
Hi all, I'm trying to figure out a way to sum the number of one column if it has the value of another column. The formula I was trying is: =COUNTIF(Scoped:Scoped, Channel22) I want to know how many are scoped in the scoped column if the channel header is in row 22. It's reading invalid. Any other ideas?
Sheet Template : When copying a template, my formula's don't migrate.
I'm trying to copy a template to use. I have formulas already on the template, however when i put the new numbers in, it doesn't use the formula to add/subtract them. I have to litterly go through and on every sheet I create from either a template or otherwise, I always have to figure out the formulas again. Is there a way…
Help needed counting across sheets
Hi all, I am trying to use the count of a symbol across some sheets. I know I can get this information by creating a report but for a more visual presentation, I am looking to get an actual number. I am currently trying something like this but it's not working: =COUNTIFS({sheet 1 Range 1}, 1, {Sheet 2 Range 2}, 1) The…
Help needed with an If/And formula referencing another sheet
Thank you for taking a look at this. I have one sheet where I am trying to show the count for a person on another sheet IF another column has a 1 in it. The 1 represents a checkbox in the ajoining column. For some I have tried looking for the specific name (where you see Courtney) and others had the name listed in a column…
Calculate days since a bubble turned red
I have a column that shows red (off track), yellow (at risk), or green (on track) depending on whether a date column (completion) is before or after another date column (goal completion date). What I'd like to show is the amount of days since a bubble turned red. This would tell me how many days it has been since a project…
Access (visibility and the option to edit) to SOME rows in a sheet
Hi, First, thanks for members of this forum, you’ve been a great help! I have one main sheet where all (different) legal tasks are listed to help the legal team manage the work load. Only the legal team has access to this sheet. One of the departments however, requires visibility of their related tasks, therefore I have…
INDEX/MATCH not working
Below are two screenshots. The first is a screenshot of my target sheet. The other is a screenshot of my reference sheet. On the reference sheet I have a list along the left. In the column to the right is: =TODAY() On the target sheet is an identical list with the following formula to the right: =IF(TODAY() = INDEX({New…
Formula to display Red Flags
I am attempting to have a RED Flag populate in the "Risk" Column for cells where the "Available Space" is less than 10. Kindly when answering, please complete the entire formula from start to end as from research I tend to get bits and pieces as seen in my screenshot. Thanks,

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

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

Watch video

Trending in Formulas and Functions

If then formula help
Hi All! I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the initiative’s average score is between: 5 and 9 it is considered a project and if the score is between 10 and 15, it is considered a program. I…
Dashboard Last Updated Date
I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet. I don't think I can do a formula in the date field type. Any other suggestions?
Counting yes and yes w/notes
Hello, I am having trouble counting using the COUNTIFS formula to yes's and another category called "Yes w/notes". Here is the current formula that I have: • =COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, @cell = "Yes") / COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is…

Formulas and Functions

Discussion List

Help Article Resources