Formulas and Functions

Archives for June 2023

Formula for Total Column
In our estimating sheet I'd like to have a locked formula that for parent rows it shows the sum of the children and on non-parent rows it shows the product of quantity*rate. I'd like it to be one formula so that it can be locked on the column. Right now I have separate formula's that from time to time will be messed up and…
Multiple IF Statements for Various Scenarios
Hello! Based on multiple criteria, I'm building a formula to yield a completion %: I want the results to yield 0% if a specific field is marked "No" or another field is marked "No" or "N/A", populate 50% (.5) if one of two criteria are met, and populate 100% (1) if all criteria are met. I started building the formula…
SUMIF formula not working, can't see why...
I've been messing with this for an hour and going crazy. Can anyone see the problem? Or suggest an alternative formula? The formula =SUMIF({8. FY24 FSS Travel Tracking Total Cost}, Appropriation@row, {8. FY24 FSS Travel Tracking Log ARU}) It is located in the cell that's been circled. It is referencing columns on this…
How to show most recent entries in Dashboard widget based on date entered?
The ultimate end result I want is to show in a dashboard the 5 most recent items that have been completed. The Primary Column on the sheet lists the names of properties, and each subsequent column shows a task that has been completed as dropdowns that can be either left blank or marked "Complete". Two of the columns are…
SUMIFS
Could SUMIFS sum the "Obligated" amount for 3 or more "Status" on the left column?
Create filename from column
I need a formula to create a name from a column that looks like this: jk.t1.t3.filename The following formula works for certain files. =LOWER(SUBSTITUTE(Filename@row, "_", ".")) I need to remove a word and underscore from specific items to create the filename. Ex item: JK_T1_T3_Test_FileName I would want the formula to…
How to create a COUNTIFS formula with OR
I am trying to create a formula across columns with different values in a cell. I have the formula for one value, Preliminary Draft. This works fine. =COUNTIFS({Comments Log Range 1}, [Document Name]@row, {Comments Log Range 2}, "Preliminary Draft", {Comments Log Range 4}, "Open") However, the value in range 2 can also be…
Mandatory "pick value from list" field in Global Update
Issue with the Global Update feature, specifically when trying to add a new value to an existing column without altering any existing values. Here's the situation: Objective: Add the value "Cancelled" to the "Status" column in the Project Plan. Approach: Global Update functionality to ensure this change is reflected across…
Formula isn't working properly
I created the below formula to calculate project schedule health and cannot seem to have it return expected outcomes. Can someone help me get this right? Red If a task is not completed and the target date is either today or in the past; OR if the end date (end date is captured when %Compete is 100%) is 5 days after the…
Formula to sum rows from one sheet based on a date range to a field on another sheet
Hi! As a brief summary, I have created a sheet for our laundry staff to keep track of how many items are being laundered on a daily basis. I have created a separate sheet for monthly totals of each item laundered. I am trying to create a formula that will sum the total number for each laundered item within each month. For…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Upcoming Events

Brandfolder Collaborator User Guide
Get ready to seamlessly upload and organize content within your Brandfolder! This on-demand session is designed to help you become a confident contributor and collaborator, ensuring your team has the right assets at the right time. What You’ll Learn: Maximize Features in the Asset Modal: Learn to navigate the asset modal,…
Brandfolder Admin User Training
Managing digital assets doesn’t have to be complicated. Join us for this two-part, on-demand webinar series designed to give Brandfolder administrators the confidence and skills to create smooth, efficient workflows. Whether you’re just getting started or ready to optimize your setup, these sessions will help you unlock…
Smartsheet Customer Success Onboarding (Japan)
Smartsheet カスタマーサクセスオンボーディングは、Smartsheet についての基本を学びたい方向けに毎週開催しているインストラクター主導のトレーニングコースです。ウェビナーは、始めるための基本、共同作業、およびさまざまな方法での作業の視覚化の基本など、Smartsheet のご利用を開始するために必要なすべてを網羅しています。

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Using SumIfs with a date qualification
Hi Everyone. I would appreciate any feedback on what I'm doing wrong. In the formula below, when I hardcode my dates below, I get the correct answer back. =SUMIFS({Tech Enablement-NCAL - Allocation}, {Tech Enablement-NCAL - Timeline Range 1}, HAS(@cell, $Resource@row), {Tech Enablement-NCAL - StartDate}, <=DATE(2026, 4,…
Stuck on a COUNTIF formula to use
Hi all I'm still trying to get my head around Smartsheet formulas with mixed success, many times i try to use the AI formula creator but it often fails for me… Im trying to create a formula that addresses the below AI query that keeps either returning an #Unparseable error or #Invalid Data Type. Basically, in my sheet i…
Is there a way to break out found Rows from a LInked Sheet into sequential rows in a new sheet?
Hi folks. Let's say I have a sheet with the following data: Then I make a second sheet that links to the first, looking for matches of both the Name and Date. What I want, in this second sheet, is to eventually show that on 12/25, Matt is Eating a Sandwich (first row of new sheet) and Using the Toilet (second row of new…

Formulas and Functions

Discussion List

Help Article Resources