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

Get a Countif on values in a multi-select column of cells
I am trying to write summary formulas to determine how many values appear of each in the drop-down of a column. I tried adding a wildcard, I have tried HAS, and tried CONTAIN, but I'm still not getting the right values. Currently I am getting 0 as a return value on this formula: =COUNTIF([ROI Type]:[ROI Type], HAS([ROI…
summaries in report
Hi i'm trying to build a report with summaries options. i have two column that i want a Min and Max summary. the column for the Min i a texte column and works well. but the other column has formulas and the Max doesn't works. there is a way to fix that? (instead of creating another text column in the source sheets?) here…
Converting email addresses with hyphenated First and/or Last Names
I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one. I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob…

Formulas and Functions

Discussion List

Help Article Resources