Formulas and Functions

Archives for October 2022

Reduce IF formula with OR condition
Hello everybody! Hope you are doing well. I am creating a board for a project that we are about to start and I want the symbol identifiers to be auto-populated based on statuses and blocks/defects. I was able to put together the formula below, which works, but I am sure I can reduce it with an OR condition, but I am not…
Grandparent status rollup based on parent status - reference parent row
I'm looking to rollup to the grandparent status cell (1) the parent status based on the status of the children. For example: If Discovery is complete and Gather Requirements children are in progress, roll up "gathering requirements" to grandparent. When that phase is complete and Delivery Planning, Build, Comms has…
Is it possible to have a requirement-
like if in this drop down "LS" is selected another cell must be populated with a $$ amount? Thank you
Is there a formula where anytime I scan a barcode it subtracts from the Quantity on hand value?
Including Checkboxes in Formula
Hello I am trying to build a formula that takes in consideration boxes that are checked off. Not sure how this would be formatted in the formula text, I will note I am referencing another sheet. This is what my formula currently looks like. =COUNTIFS({2022 Committed Project}, @cell = "Is Checked", {Phase}, @cell =…
INDEX COLLECT formula
I am having some issues with the index collect. I am trying to return data from one sheet looking at two columns from another. I have watched multiple videos on this and I cannot seem to get it right. Currently I have: =INDEX(COLLECT({Asset ID}, {Maker Codes}, [Maker Line]@row, {Packer Code}, [Packer Line]@row, 0)) Asset…
COUNTIFS and CONTAINS
Hi. I have a multiple list column [Teams Responsible for Testing] where I want to count the number times MIS is listed when data in the [Status] column <> Complete. What is my formula? Thank you.
Formula Help!!
I would like to "Count" how many days a person has called out that are "Unexcused" in the last 12 months on our active spreadsheet. Anytime we have an employee that calls out on our line a "Form" is filled out that helps us track who called, date they called, time they called, reason for call, whether it is excused,…
Formula to Extract a Full Name and Title, into Separate Columns "First" "Last" "Title"
I have a long list of full names in the "Full Name" Column. They need to be broken out into 3 separate columns "First Name" "Last Name" and "Title" Example shown in the screen shot below. Is there a formula I can use to extract this for each column? "First Name" I've been using: =MID([Full Name]@row, FIND(" ", [Full…
Countifs formula
Hi, I want count where date column is not blank and country is Australia. Example is below where Smartsheet completion is not blank and country is Australia so need to calculate count of dates. How to write Countifs formula with not blank as criteria. Have tried following formula but it shows error: =countifs([Smartsheet…

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

Formula working for one data value but not another
Hi Community, I have a sheet that contains, among others, columns titled "Priority" and "Contract ID". Multiple rows can have the same Contract ID and the same Priority. Priority values are 1-2-3 and restricted to dropdown list values only. To collect the number of unique Contract IDs that are Priority "1", I have this…
COUNTIFS formula across sheets
Hi all I've been using Sheet Summary's to calculate some data which seems to work quite well. For instance this is an example of a Summary that is working fine. (Its an ISO27001 audit tracker sheet) =COUNTIFS(Finding:Finding, "Opportunity for Improvement", Status:Status, <>"COMPLETE", [Agreed closure date]:[Agreed closure…
Need Help With Date Calculation Formula
Hello, I have 2 date columns. I put a test sheet together for a visual reference. The Date 2 column needs a formula to calculate whichever date 39-45 days out is a Friday. Any help would be greatly appreciated.

Formulas and Functions

Discussion List

Help Article Resources