Formulas and Functions

Archives for September 2020

Parent/Child Status
Hello, I'm trying to get a setup going in my project templates where the parent cell will update based off the input of of the children cells. For example, the main parent is "Onboarding", with children "Licensing", "Credentialing", "DEA". Then, each of those children has their own children to track individual providers.…
Can you change these labels?
Would like the blue and red to have a different label. TIA
Sheet Summary: COUNTIFS formula to count up completed tasks
I have a schedule sheet with nested task sections. e.g., Parent Row is called "Copy Editing" and underneath that are children rows for 15 book chapters with Task Names like "Copy Editing - Chapter 1", "Copy Editing - Chapters 2-3", etc. I want a Sheet Summary formula that counts how many chapter rows have a value of 100%…
Intake form updated daily how-to...
We have a medical intake form that we have to fill out daily I am trying to figure out how to keep the employee name in only one row and just update the screening details daily not have 300 lines for one employee. We will keep the archived details on another form so I have it set to copy to another form.
Log the date and time a cell changes to a specific value
Hello. I've got a sheet I've developed to track approval of purchase agreements. After an agreement makes its way through the approval process, I have the cell "Final Approval" display the words Agreement Final Approval, and the cell is conditionally formatted to red. The main users would like me to log the time and date…
SUM all payments made in the last year
Hi, I'm trying to add up all the payments in a column that were made in the last 365 days from the current date. The date the payment was made is in a second column, formatted as a date column. I have written this formula: =SUMIF([Payment 1 Paid Date]:[Payment 1 Paid Date], TODAY(@cell) >= TODAY(-365), [Payment 1]:[Payment…
Nested IF statement with a checkbox
This discussion was created from comments split from: IF, AND, OR Functions.
Formula to check a done box with if and or
I am trying to create a formula to check a Done box if the Status = Done or if the Status = N/A. Here is what I have =IF(Status25 = "Done", 1, 0), OR(Status25 = "N/A) but I am getting the unparsable error. Does anyone know what I am doing wrong?
Formula to add incoming inventory to existing inventory?
Hello, I am trying to figure out a formula for my inventory sheet. I am trying to create an automatic formula that when a shipment is marked received, it will update the inventory sheet. I thought it would look like =SUMIFS({Customer Order Tracking & History Range 1}="Received"AND{Inventory Management 2.0 Range 1}@row ) so…
Ignore blank Cells
Hi I'm trying to get the following formula to ignore blank cells =INDEX(([1]:[1]), MATCH(MAX(Created:Created), Created:Created, 0)) Can't quite get it to work.

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 Can I Calculate the Number of Hours between Two Different Time Formula Values?
I have a form that collects times of day, into a sheet via a dropdown List of times (9:00 AM, 9:30 AM, 10:00 AM, ect). I have converted these values into a time using the TIME function. So now I have 4 columns: Start Time (dropdown list value from the form) End Time (dropdown list value from the form) Start Time 12 Hr…
Automatic Phase from WorkBreakdown
Hello all! I'm modifying the Simple Portfolio Work Breakdown Schedule and having a little trouble. I like the way the Work Breakdown structure works but I'd really like an automatic way to track which Phase something is in. I know I could just add a column and manually select which Phase tasks and sub tasks are in, but I'm…
Join Formula, bringing data from one sheet to another for Procurement use
Hello, I'm new to Smart Sheets and I'm trying to navigate myself through this new world. Long story short, I'm building a procurement system from the ground up for my current company. I've created an RFQ sheet / Workplace, I've figured out how to let my team fill out forums to populate rows and used Document builder to…

Formulas and Functions

Discussion List

Help Article Resources