# Formulas and Functions

Let the community help you with that tricky formula or function! See how others are utilizing the power of formulas and functions to save them time and create more powerful solutions with Smartsheet.

## Discussion List

• Accepted Answer Pam Bussing 12 views 4 comments Most recent by Andrée Starå
First post 12/17/19,Last post 12/17/19
Hello, I have an "End" date column and I can calculate out 12 months using: =[End]@row + 365 However, I would like to have this date populate the cell only if the "%Complete" cell, in the same row, is 100%. In other wor…
Formulas and Functions124
Pam Bussing
• Answered - Pending Review Emily Alpert 55 views 1 comment Most recent by Andrée Starå
First post 12/17/19,Last post 12/17/19
Hello, I'm trying to count the number of projects delivered in the past seven days. This is what I have so far: =COUNTIFS(Status:Status; "Delivered";Delivery Date:Delivery Date; >=TODAY(-7)) I'm not sure what's going wrong or…
Formulas and Functions551
Emily Alpert
• Accepted Answer Brian Erdelyi 22 views 3 comments Most recent by Brian Erdelyi
First post 12/17/19,Last post 12/17/19
I am using the following formula to change a date into a billing quarter. =MONTH([Year-1/Deliverable-1 Invoice Date]@row) / 3 It works well except for any date in January. That returns a value of .3 (1/3=.3). The system automatically roun…
Formulas and Functions223
Brian Erdelyi
• Accepted Answer GeoSmile :-) 420 views 8 comments Most recent by Andrée Starå
First post 07/19/19,Last post 12/17/19
Hello all,  Now that formulas can be used in the contact column - fantastic!  - what is the syntax to reference a particular contact value? - I am trying to set up an IF <columnA value> ="blah" then assign contact X. I ha…
Formulas and Functions4208
GeoSmile :-)
• Sum all RowsQuestion
Answered - Pending Review Ramsay Zaki 275 views 3 comments Most recent by Jim Schaffhausen
First post 11/04/19,Last post 12/17/19
My sheet has 3 columns (Team, Activity, Amount). I have a summary section at the top of my sheet where I want to use a SUMIF function to total up the amount for each Team. Generally, I can get the SUMIF function to work just fine. But, …
Formulas and Functions2753
Ramsay Zaki
• Accepted Answer Silvia Rangel 21 views 7 comments Most recent by Andrée Starå
First post 12/17/19,Last post 12/17/19
Hello, First time posting here ☺ Trying to setup a flag for when the same data is entered in a row, but it's giving me a hard time ☺ help? (Image)
Formulas and Functions217
Silvia Rangel
• Answered - Pending Review Técnica Engenharia 21 views 8 comments Most recent by Andrée Starå
First post 12/16/19,Last post 12/17/19
I want to check by dates if you need to call the customer by activating the red flag, this red flag will serve as a pending call to a customer CRM spreadsheet. It is working correctly, it happens that when the date cell is empty the flag …
Formulas and Functions218
Técnica Engenharia
• Answered - Pending Review Natalia 43 views 8 comments Most recent by Paul Newcome
First post 12/16/19,Last post 12/17/19
I am attempting to write a formula based on 2 separate criteria, though I feel like I need some type of "BUT" statement and could use some help. I want a picklist column to read YES or NO depending on the following: IF Ins Status…
Formulas and Functions438
Natalia
• Accepted Answer Jamila 269 views 2 comments Most recent by Jamila
First post 12/16/19,Last post 12/17/19
I am trying to develop a formula to flag tasks that are less than 50% complete but more than 50% of the duration has passed. I'm not sure if my math is correct. But aside from that, can Smartsheet perform functions like the one I am propos…
Formulas and Functions2692
Jamila
• Answered - Pending Review Rob Pritchard 56 views 11 comments Most recent by Andrée Starå
First post 12/14/19,Last post 12/17/19
We have a master job list which we use to create our new project references. There are a lot of requests with a range of values ? I have a separate sheet where we populate with those projects we want to keep a closer eye on costs on picked…
Formulas and Functions5611
Rob Pritchard
• Answered - Pending Review TRach 135 views 2 comments Most recent by Paul Newcome
First post 12/05/19,Last post 12/17/19
I am trying to change the drop down value on my Health column based on the current date. Is this possible? ie. If the start date value is in the past, the health column changes to "At Risk" which is one of the drop down choices.
Formulas and Functions1352
TRach
• Answered - Pending Review Netanel yosef 276 views 7 comments Most recent by Paul Newcome
First post 12/15/19,Last post 12/17/19
hi, i have task in smartsheet and each task i give factor which amounts 100%. and each task i give actual % complate. the problam is when i try to calculate the Weighted average i need to multiply each task in his factor. if i delete or ad…
Formulas and Functions2767
Netanel yosef
• Answered - Pending Review Técnica Engenharia 21 views 1 comment Most recent by Andrée Starå
First post 12/17/19,Last post 12/17/19
I would like to make a formula for calculating percentages based on the progress of the task as we contract projects. When you set the option "completed" the formula passes the value of 100%. When "in progress" the fo…
Formulas and Functions211
Técnica Engenharia
• Answered - Pending Review Stavros_McGillicuddy 126 views 2 comments Most recent by Stavros_McGillicuddy
First post 12/16/19,Last post 12/17/19
I have a [RowID] column that is populated by this formula =IF([email protected] = 0, COUNT(\$ANCESTORS\$1:[email protected], "")) It returns sequential numbers but, I need it to only count if Ancestor = 0 Secondly, I need the result fill …
Formulas and Functions1262
Stavros_McGillicuddy
• Answered - Pending Review James Emge 34 views 12 comments Most recent by Andrée Starå
First post 12/14/19,Last post 12/15/19
I am working on a display for RYG indicators where my data collection sheet will look at today's date, a site that has either confirmed a check, failed one or has not performed it yet. I have pieced this together from some of the terrific …
Formulas and Functions3412
James Emge
• Brandon Schwartz 2.2K views 3 comments Most recent by Gwyneth C
First post 06/08/16,Last post 12/15/19
Hello,   I am trying to create a nested IF formula based on the criteria below. Below is also a screenshot of what I am trying to do:     =IF([% Complete]1=0,"Not started" =IF([% Complete]1>=0 and <=1, "On trac…
Formulas and Functions21653
Brandon Schwartz
• Michael W. 285 views 4 comments Most recent by Andrée Starå
First post 11/13/19,Last post 12/12/19
Greetings, In a Contact Column, and in a ancestor/child relationship, I would like for parent rows to summarize/list all persons in the child tasks.  Is Smartsheet able to perform this action? E.g. If the child records list: * Jo…
Formulas and Functions2854
Michael W.
• Sldollman 55 views 6 comments Most recent by Andrée Starå
First post 11/12/19,Last post 12/08/19
Hi community, I'm attempting to find a formula that will return the parent row where the end date is equal to the current week.  I have created a helper column that calculates the current week number.  Now the question is what to do with…
Formulas and Functions556
Sldollman
• Chris Trumpey 83 views 1 comment Most recent by Genevieve P
First post 12/06/19,Last post 12/08/19
Hi All, I need some CountIFS help with counting dates, I have a sheet that has a column called Device that has either New or Existing. Then my second column is Date Installed. If the Device is New and the Date Installed has a date then I …
Formulas and Functions831
Chris Trumpey
• Jon Stikka 187 views 4 comments Most recent by Andrée Starå
First post 12/02/19,Last post 12/08/19
I would like to set up Sheet Summary to show a counts of Open Tasks, and Completed Tasks - based on the column "Done" being checked or not.  The "Parent rows" are for reference only, they are not Tasks and shouldn't be…
Formulas and Functions1874
Jon Stikka
• mel_berk 129 views 3 comments Most recent by Andrée Starå
First post 12/03/19,Last post 12/08/19
How do I format my At Risk column so the flags only appear in the "children" rows, as shown in the screenshot? (Image)
Formulas and Functions1293
mel_berk
• lisarae723 83 views 6 comments Most recent by Andrée Starå
First post 12/04/19,Last post 12/08/19
Hi everyone! Im having issues with a status column. My goal is to update the status if a job has hit 10 days or more and has not been approved yet. I was going to trigger it by using the (system column) "Last Modified"  Here's …
Formulas and Functions836
lisarae723
• lesmickin 61 views 1 comment Most recent by ron.judenberg112096
First post 12/05/19,Last post 12/07/19
I have a column that has approx 20 multi-select options.  I am wanting to run a report that shows a count of any instance in the sheet where let's say HSW has been selected.   Column is annotated as a multi-select column/field.  Column na…
Formulas and Functions611
lesmickin
• Paul Newcome 32 views 6 comments Most recent by Andrée Starå
First post 12/06/19,Last post 12/07/19
Has anyone been able to figure out how to do a find/replace on a checkbox type column?   I have tried 1, true, TRUE, True, "1", "true", "TRUE", "True", and none of them seem to trigger a match.
Formulas and Functions326
Paul Newcome
• janger 25 views 1 comment Most recent by Andrée Starå
First post 12/06/19,Last post 12/07/19
Is there a way to count the number of rows of text within a cell?  I know this will change dependent on column width but if I keep my column width static, I need to know how many rows of text I have within a cell.
Formulas and Functions251
janger
• kolfinna 47 views 3 comments Most recent by kolfinna
First post 11/19/19,Last post 12/06/19
Hi everyone, Fairly newish user when it comes to formulas here; please forgive me if this has been answered elsewhere and I missed it in my searching. I'm wanting to assign a value to a column named Bucket based on the value in the Start…
Formulas and Functions473
kolfinna
• kane.godfrey 31 views 3 comments Most recent by kane.godfrey
First post 12/05/19,Last post 12/06/19
Hi,  I need help to solve this puzzle. I'm using this sumif formula   =SUMIF({Supplier Invoice register Order Number}, [PO Main Contract]10, {Supplier Invoice register value}) to sum up the total value of invoices against a particular o…
Formulas and Functions313
kane.godfrey
• slilienfeld113476 140 views 1 comment Most recent by Morgan Marquez
First post 12/05/19,Last post 12/06/19
I'm attempting to create a formula that will count the number of cells in a column from range in another sheet, that only includes the cells with a checked box.   In other words, there are two columns to include in the count from my Comm…
Formulas and Functions1401
slilienfeld113476
• GrahamR 22 views 5 comments Most recent by [email protected]
First post 12/05/19,Last post 12/06/19
Hi,   I'm setting up a page where you enter 5S audit test data from a Form. It puts a new row in every time with the 5 different 5S scores each in it's own column. We need to find a way to pull the data from the most recent new row. I n…
Formulas and Functions225
GrahamR
• Michael Gerdes 32 views 2 comments Most recent by Richard Rymill SBP
First post 12/05/19,Last post 12/06/19
Hi, I have an inventory sheet where users are putting products in a box, the user would like to minimize the number of clicks as they are moving large number of products a day but these products will go into several boxes. is there a way t…
Formulas and Functions322
Michael Gerdes