Formulas and Functions

Archives for April 2020

Can you auto populate task dates based on the entire project end date?
I have different marketing tasks where their completion dates revolve around an entire projects completion date. For example, there are tasks that need to be completed 10 days before project completion date, 20 days prior, 30 days prior, etc. Is there a formula I can use that auto fills task due dates based on the project…
Need to apply conditional formatting to a parent row when a child row date is in the past
In this particular instance, I want the "Production" row to display in Red/Bold since it has subtasks that are past due and not ticked "completed". Is there a way to do this easily?
IF Checked, Assign Auto Number
Hi there! I'm no excel or Smartsheet formula wiz but I'm hoping someone here can help me figure out a way to do the following: I'd like an auto-populated number to appear in a row when the "Estimate" box is checked. There will be some rows where estimate is not checked and therefore I don't want to dedicate a row to a…
Create a Duplicate Flag
Hello, I am trying to create a flag that turns red when a duplicate name is entered. Here is the formula I have so far, but it keeps telling me it is "#Unparseable" =IF(COUNTIF([Client/Resident Name]:[Client/Resident Name]@cell) > 1, 1)
Convert duration in a number column to make calculations
How could I covert the duration information/data in number so I can calculate variances in numbers or in percentages? I had to create a column and type each number, but I can not do that in 50 different worksheets. Is there any formula that can convert the duration days in real numbers so I can use it for additional…
How can I use Smartsheet to help us with my Pitch deck?
As a radiopharmaceutical company, we have many Pitch decks in order to get grant money. Over the years, I have multiple pitch decks. Is there a way that Smartsheet could somehow help me organize those pitch decks (presentation is in powerpoint format). I am wanting to somehow organize my slides by content (example all…
Parent cell - Status symbol total
Hi there, How can the parent cell (circled in blue) automatically reflect the correct color/symbol concerning the tasks below? When I say automatically, I refer to do it by default, not me selecting the symbol all the time. Can this be done with a formula, and if so, which formula? Thanks in advance M
countif by date references
I have a column of dates in one sheet called {Dates} In a second sheet I want to analyze the dates based on if they fall into a specific calendar month I have a column called Y (year) filled with data like 2020, and a column called M (month) filled with data like 1 (i.e. January) using the following formula:…
COUNTIF Help
Hello, Is there a way to count the number of times a contact appears in a certain column, if there are multiple contacts in some of the cells? For example, I can use COUNTIF to see how many times Charles appears in cells where it is just his contact only, but I want to count how many times Charles appears even if there is…
Help with IF(INDEX(MATCH
Hi all, First question in the forum. I'm in need of help with an INDEX(MATCH( formula that I've been using but now need to make conditional on another column. Essentially, I only want the INDEX(MATCH( formula to return a value when a column "Completion" is changed to "100%". I've tried nesting the existing INDEX(MATCH…

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 determine if all children have N/A Status
I'm working on a formula for summary rows to look at the % Complete of the row as well as the statuses of the child rows. I have the % Complete portion handled: =IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In Progress", IF([% Complete]@row = 0, "Not Started"))) I want to add in…
I am trying to manipulate my SUMIFs Formula
When Using SUMIFS(Range, Criterion Range 1,Criterion...) I have multiple columns for example Estimate Spend, 25% spend, 50% spend, Rather than having multiple %'s of spend I just want to be able to use 1 reference and multiply it by 0.25 or 0.5 in the first "Range" category so =SUMIFS({Spend} * 0.25, {Phase}, "phase 1",…
Quiz scoring
Need a proper formula to count each correct answer on a row. with inspections it is safe or at risk which is easy to count, but i can figure out how to count each column with different answer. I came up with this =COUNTIFS([Reason forklift safety is important]@row, "D. All of the above", [Primary sources of fuel]@row, "D.…

Formulas and Functions

Discussion List

Help Article Resources