# Formulas and Functions

## Discussion List

• Accepted Answer Marcel Hovsepian 14 views 2 comments Most recent by Marcel Hovsepian
First post 08/13/20,Last post 08/15/20
Hello, I have looked through Q&A and went through formulas tutorials. My formula appears correct to me but keeps on returning #UNPARSEABLE in my Project Health column. Any help or if someone can point out what might be happening, it …
Formulas and Functions142
Marcel Hovsepian
• Answered - Pending Review Monica Gallegos 9 views 1 comment Most recent by Kelly Drake
First post 08/14/20,Last post 08/14/20
I'm deep over my head in a couple formulas. In the screen grab below you'll see Location and the various formulas that follow: Count based on sheet: =COUNTIF({ERG MEMBERSHIP EA Location}, @cell = "REMOTE") Previous Month - count …
Formulas and Functions91
Monica Gallegos
• Answered - Pending Review K L 16 views 2 comments Most recent by Kelly Drake
First post 08/14/20,Last post 08/14/20
I'm trying to figure out how to build a formula with this logic. When a person is listed on a row with start and end dates, are they listed again for an upcoming event in the next week? Is it possible to create a formula that gives a yes/n…
Formulas and Functions162
K L
• Accepted Answer tylermh 15 views 7 comments Most recent by Kelly Drake
First post 08/14/20,Last post 08/14/20
I'm looking for an IF/COUNTIF formula for automatically flagging the flag symbol column when a contact's name appears in a Contact column for a 3rd time? (incident tracking report) (Image)
Formulas and Functions157
tylermh
• Answered - Pending Review Sidonia Posteuca 15 views 3 comments Most recent by Sidonia Posteuca
First post 08/14/20,Last post 08/14/20
Hello, I do not know if this is possible, however it would make life so much easier if it were. Can I set up a formula in one column to calculate how many people (man power) I need on a specific job based on the start and end date (when t…
Formulas and Functions153
Sidonia Posteuca
• Answered - Pending Review Taylor Riley 20 views 5 comments Most recent by Taylor Riley
First post 08/14/20,Last post 08/14/20
Hi! I'm attempting to use the below index/match formula to automatically assign contacts based on 1 - 2 criteria. However, I keep getting #UNPARSEABLE error. The [Assigned To] Column is setup as a contacts column, and I have the contacts l…
Formulas and Functions205
Taylor Riley
• Accepted Answer Susan Hildebrant 18 views 6 comments Most recent by Susan Hildebrant
First post 08/12/20,Last post 08/14/20
I am trying to create a formula that will find the correct value in a column in one sheet and paste the value of another column on the same sheet and "paste" on a second sheet. I tried many formulas and can't find the right one. …
Formulas and Functions186
Susan Hildebrant
• Answered - Pending Review Jeana 16 views 4 comments Most recent by Jeana
First post 08/14/20,Last post 08/14/20
I'd like to set up a check box or some other trigger that tells the Auto number column to do its thing. This will NOT always (or usually) be a new row and it will be a row with Children (and it's a Child too). That's why I'm looking for a …
Formulas and Functions164
Jeana
• Accepted Answer Jeana 27 views 10 comments Most recent by Paul Newcome
First post 08/13/20,Last post 08/14/20
I have a column called Project with a PARENT row called PROJECT 1. I am looking for a formula to use in another column (WHATS NEXT) that will return the value of the next CHILD row, until there are not more CHILDREN. Appreciate any insight…
Formulas and Functions2710
Jeana
• Accepted Answer Laurie Bearden 45 views 9 comments Most recent by Paul Newcome
First post 03/13/20,Last post 08/14/20
Good morning/afternoon. I have been searching in the help articles and community for an answer to my question, to no avail. I have tried a few different suggestions but nothing has worked so far. Here is my dilemma. I have a sheet that…
Formulas and Functions459
Laurie Bearden
• Accepted Answer Mily Oti 7 views 6 comments Most recent by Mily Oti
First post 08/14/20,Last post 08/14/20
Hello, I'm trying to create a formula that will look at a language, the agency, and the status and fill in \$ amount. I have written six different formulas that all work but when I try to combine them I get the error message, incorrect argu…
Formulas and Functions76
Mily Oti
• Answered - Pending Review caw98776 11 views 3 comments Most recent by Andrée Starå
First post 08/14/20,Last post 08/14/20
Is there a formula that would allow Smartsheet to automatically enter the current date in a separate column when a drop-down choice in another column is selected. For example, when the Progress column is updated (through the drop-down) to…
Formulas and Functions113
caw98776
• Accepted Answer Emerus CCC 13 views 3 comments Most recent by Emerus CCC
First post 08/07/20,Last post 08/14/20
I am trying to reference an automated creation column from sheet A to populate date AND time in a column in sheet B. I have been successful in the reference, but then have to format the receiving column to date format due to invalid data…
Formulas and Functions133
Emerus CCC
• Accepted Answer Ruth Potts 33 views 3 comments Most recent by tylermh
First post 07/02/20,Last post 08/14/20
Hi, (Image) I have used the formula =IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), 1) in the flag column to turn the flag red when the end date is passed and completion is less than 100% which is great. BUT.... It now red …
Formulas and Functions333
Ruth Potts
• Answered - Pending Review William Young 10 views 1 comment Most recent by John Jonassen
First post 08/14/20,Last post 08/14/20
I used the formula =sum(qty1 * cost1) to get my total cost. I copied that formula down my total cost column and have a bunch of blank rows showing \$0.00. How do I adjust my formula to leave empty rows blank?
Formulas and Functions101
William Young
• Accepted Answer Paige Luben 12 views 2 comments Most recent by Paige Luben
First post 08/13/20,Last post 08/14/20
=COUNTIF(AND({2020-2021 Goal, Budget, and Performance Ti Range 2},"Budget",[{2020-2021 Goal, Budget, and Performance Ti Range 1},"Complete"])) =COUNTIFS(({2020-2021 Goal, Budget, and Performance Ti Range 2},"Budge…
Formulas and Functions122
Paige Luben
• Answered - Pending Review JO Ross 7 views 2 comments Most recent by JO Ross
First post 08/13/20,Last post 08/14/20
I am trying to enter a formula that returns a 1 or 0 if the word "accepted" is in a reference cell. I am ultimately trying to run this formula for 3 separate cells, and if the sum of those cells is 3, then the general status colu…
Formulas and Functions72
JO Ross
• Answered - Pending Review Halil Olsen 8 views 2 comments Most recent by Andrée Starå
First post 08/14/20,Last post 08/14/20
I currently have a sheet with Smartsheet Licensed Users in our company, we want to check the last time each user logged in to Smartsheet so that if we think they do not require a license we can remove it from their account. I currently do …
Formulas and Functions82
Halil Olsen
• Accepted Answer Courtney Caswell 25 views 7 comments Most recent by [email protected]
First post 08/12/20,Last post 08/14/20
I am struggling to put together a multiple part formula that only does the average of a column if certain factors are met. I want to take the average number in column [NetDays] only if done is check and the row in column [Event Location] …
Formulas and Functions257
Courtney Caswell
• Formula for % complete.Accepted Answer
Accepted Answer Laura Bell 9 views 4 comments Most recent by Laura Bell
First post 08/13/20,Last post 08/14/20
Hi. I have a sheet made up with the column below. I am trying to make the percentage complete auto-fill based on which boxes are checked. I am not sure how I can efficiently do this. I can't figure out how to "add" the check …
Formulas and Functions94
Laura Bell
• Answered - Pending Review Matt Babcock 24 views 5 comments Most recent by Matt Babcock
First post 08/11/20,Last post 08/14/20
I'm making a project template that feeds a portfolio page and to make the portfolio page easy to update I set a goal to only use a single cross sheet reference between projects. Overall it's working well but I'm stuck on one part. If my us…
Formulas and Functions245
Matt Babcock
First post 08/12/20,Last post 08/14/20
Is there a formula I can use so that when I'm creating a budget burndown, that if I don't have the actual numbers yet, that it will use the forecast instead? (Image)
Formulas and Functions174
• Answered - Pending Review Laurie A 12 views 2 comments Most recent by Laurie A
First post 08/14/20,Last post 08/14/20
Hi All, I'm working on creating dynamic metrics using a roll up metrics sheet. I'd like show the total number of tasks for each status and the total that has no status assigned. Easy, right? =COUNTIF({Column Name},"Status Type") …
Formulas and Functions122
Laurie A
• IF functionQuestion
Answered - Pending Review Laura_Murphy 16 views 5 comments Most recent by Laura_Murphy
First post 08/13/20,Last post 08/14/20
Hello I am trying to write a formula that will automatically fill the 'status' column as Confirmed if the 'Booked' Column has a date input but not if it is blank? I would be grateful for any help you can provide
Formulas and Functions165
Laura_Murphy
• kari1282 96 views 3 comments Most recent by David Joyeuse
First post 11/19/19,Last post 08/14/20
Hi I'm trying to to create a sheet that tells me all of the timekeepers that work on a client from data from another sheet.  Currently I have sheet that list all of our clients and I will like to add a column that list all of the timekeep…
Formulas and Functions963
kari1282
• Accepted Answer Leslye Jackson 32 views 30 comments Most recent by Leslye Jackson
First post 08/12/20,Last post 08/14/20
Hello All, I am working on using a drop down box to calculate task percentage as well as the complete percentage total per phase. Initially we were using the % Complete and allowing any percentage to be entered however now we are going wi…
Formulas and Functions3230
Leslye Jackson
• Accepted Answer Courtney Caswell 11 views 5 comments Most recent by Paul Newcome
First post 08/13/20,Last post 08/14/20
I am working on a report to show the last 10 items that have been completed with a criteria. I saw on another post that they recommended having a helper column that will check if it was the recent but didn't have an example. I am looking t…
Formulas and Functions115
Courtney Caswell
• Accepted Answer Chris A 11 views 5 comments Most recent by Paul Newcome
First post 08/13/20,Last post 08/14/20
I would like to see changes to the report criteria that determines what data is pulled into the report. I have a few different admins that have access to a report. What the report pulls in from the sheets keeps changing, and I want to see …
Formulas and Functions115
Chris A
• Answered - Pending Review Taylor Riley 23 views 8 comments Most recent by Paul Newcome
First post 08/11/20,Last post 08/14/20
Hi! I have created a form that marketers will use to submit requests. We want to track the number of requests that we are receiving each week. Is there a function I can use in the summary data to capture this? Or is there a way to run this…
Formulas and Functions238
Taylor Riley
• Answered - Pending Review Jeana 38 views 7 comments Most recent by Paul Newcome
First post 06/06/20,Last post 08/14/20
How do I get Index/Match to do the following: Look at Source sheet column for a Match in a Parent row and if there is a match, pull in two date columns for all the CHILDREN. This formula is pulling in dates but only for the first PARENT/C…
Formulas and Functions387
Jeana