Formulas and Functions

Archives for September 2018

Countif formula... using crosssheet formula
I've tried both... they don't work they say unparseable... what am i missing I want to I am looking to add up all P1's that have a region of America Break Fix and a status that is not finished or cancelled, then count all the P1's in the priority column. =COUNTIF({CS Current Months WO status - Status}="Open",{CS Current…
Auto changing status based on due date
Hi, i am hoping this is possible and someone can help? We have created a sheet which is populated from a form and is working really well. We populate the "Required completion date" base on the type of issue and this is automated and again working very well. What i would like to achieve is an automatic change to the status…
Using decision symbols to change text
Please can somebody help me, I'm new to formulas! I'm trying to get text in a cell to change depending on what decision symbol has bee selected (YES, NO, HOLD) I managed to get it working with just 'YES' as follows: =IF(Graphic6 = "YES", "Includes GRAPHICS see schedule") but I also want different text "No GRAPHICS" to…
Hybrid Report/Sheet
I'd like to use Smartsheet to track performance on a multitude of projects. I can use a Report to quickly gather a list of assignments and compare Need Date to Date Completed. But I can't add any additional columns (i.e. with formulas to display graphics based on whether the assignment was completed on time) or additional…
Join and Collect Function with Hyperlinks
Hello All, I got my function to work the way I wanted it to, but it did not keep the hyperlinks when it gave me the values. Is there a way to keep the hyperlinks or add them back when using Join(Collect())? =JOIN(COLLECT({Sandbox - DR - Deliverable}, {Child SKU}, =[Child SKU]1, {Sandbox - Delivery Readiness Tracker Range…
Auto filling a cell based on the entry in another cell
Hi, We have created a form to capture feedback from several stores we manage. How can i get the Store managers name to auto populate on my sheet based on the store name? I dont want this on the form just on my sheet when the entry has been submitted. Sorry i am really struggling with this and fear it is around Vlook up…
Listing Options in IF formula
I've looked at the forums and tried as many variations in the formula as I can think of, so I need some help. My two columns (both drop downs): Payee Name Payee Position *(location of formula) I'm trying to get Payee Position to automate the position title for each payee. Here's what I've got to work for one position…
Automated Checkbox column
Hello - I am trying to make my checkbox column automated so that when the PARENT rows is checked all the CHILD rows get checked. I can only find help on the reverse. Can someone help me write that formula? Thank you Erin
SUMIF statement based on dates
I would like to create a SUMIF equation to only sum data (column F) based on a date range (column B) . It works in excel with the following equation for reference. =SUMIF($B$9:$B$2000, ">=9/1/2018", $F$9:$F$2000)-SUMIF($B$9:$B$2000, ">=9/30/2018", $F$9:$F$2000) However, when I import it to smartsheets, I get an error. My…
Trying to calculate dates
I'm trying to have smart sheets calculate a new date. I'm using =[EndDate]5+1 but it's not returning a date. What am I missing?

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

How to return the last recorded date in a column?
I have a % Complete column and a deadline column. I'm trying to write a formula for when [% Complete]16 equals 100% it returns the last recorded deadline. I'll use this to record the end date of a project. [% Complete]16 is the bolded cell which is averaging the children. The deadline that should return from the formula is…
What am I doing wrong?
Shows #Incorrect Argument set =IF(AND(IF([Jan Scheduling Location]@row = "BPHO|Belen Public Health Office", [Jan Appt Type]@row, "Err1"), IFERROR(INDEX(DISTINCT(COLLECT([BPHO|Belen Public Health Office AT]:[BPHO|Belen Public Health Office AT], [BPHO|Belen Public Health Office AT]:[BPHO|Belen Public Health Office AT],…
Nested IF Statements to Determine Priority
Hi everyone, I've written nested IF statements for a Risk Register, looking at Probability and Impact to determine the color. It's been a while since I've done this and I'm obviously rusty. Can someone tell me why the following is unparseable? =IF(AND([Probability]@row = "High”, [Impact]@row = "High”), “Red”,…

Formulas and Functions

Discussion List

Help Article Resources