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!

Trending in Formulas and Functions

Trying to search 1 column of % to correspond with the column next to it. First % less than 100%
I am trying to search this column for rows 11 through 19 starting top and working towards the bottom and once a cell has a % in it less than 100% i want it to reference the cell in the column next to it. I have a formula that works if its 0% but not anything less than 100%. Formula that works for 0% - =INDEX([Task…
Status Update Formula
Hi team, I have lost one of my most favorite formulas as a project manager. Here's what I'm trying to accomplish: If % Complete is 100% - select "Complete" for the status column. If % Complete is between 1-99% - select "In Progress" for the status column. If % Complete is 0% - select "Not Started" for the status column.…
Check Box for Current Milestone
I am looking for help in writing a formula (or using automations) so that the most recent milestone where the status is "INV PKT SENT & PSR XL UPDT" will have the checkbox. As you can see, I can write a formula to check the box but then when the next milestone reaches that same status I now have multiple checkboxes in the…

Formulas and Functions

Discussion List

Help Article Resources