Formulas and Functions

Archives for January 2022

Capture Last Number in a Sequence?
Hi- I have these lesson ranges across columns on a row: 1-4; 5-11; 12-16; 17-22 What formula can I use to capture the last number in the sequence (22, in this case)? Thanks in advance, LGW
Indenting on smartsheet causing calculation error- help?
Hello, My organization uses smartsheet as a scheduling tool. We have a column that calculates what resource hours we have remaining for available scheduling (formula =((# of crew members * work days * work hours) - SUM(CHILDREN())) in my case specifically, =((8 * 5 * 7) - SUM(CHILDREN())) ) If a work order has multiple…
Count nr. of checkboxes based on another value
I have a sheet with actions. It has a status column (in progress and not started). Further in the sheet I have checkboxes per department. I'm trying to build a report, that shows the number of actions that have status in progress for the specific department. I can do the count of the checkboxes. but need to have the…
Need to Tally and List Unique Values From a Drop-Down
Hello, I have a sheet in which I am tracking distributor complaints. I need to make a few different graphs. One of which is supposed to be a line/tally graph of how many complaints have been made by each distributor. We have a list of almost 40 different distributors which we have in a drop-down select form, for the ease…
Overwriting column formulas
Hi folks, I've got a lovely column formula doing what I wanted it to be able to do. But, it seems that individual cells in the column are now uneditable. That was not my plan! Is there anything I can do to make the column formula apply in the first instance but then allow the cell to be editable in the second instance?…
combining them in the same column
Hi, Can we show the date and time by combining them in the same column? thanks
How to convert date to plain text like ()20221001112830
I am trying to convert the date(2022/10/01 11:28 AM 30 sec) into 20221001112830 this format can any one help how it will be get it done. Thanks
Using =COUNTIF Function in Sheet Summary returning #INVALID DATA TYPE
Hi, I am trying to create a formula that will count the # of project that are ">90" days out and "<=90" days out, then also projects that are "<=-90" days past complete date and ">-90" days out but it is returning an error #INVALID DATA TYPE error for sheet summary field (see screenshot below). I am using the "# of days to…
Help with date range formula
Hi, I have a sheet with a "Helper Date" column and a column which shows if a participant registered for an event or not. I'm looking for the total of registrants (attended or not) during a quarter of a year. I have two formulas that work now: The amount of events per quarter: =COUNTIFS([Helper Date]:[Helper Date],…
Is there such a thing as COINTAINSIF?
Which function do I use to count the number of times certain letters eg PRJ and TRN appear in a range if in another column the project is open (not closed).

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 のご利用を開始するために必要なすべてを網羅しています。
Visualize Project Data with Smartsheet Dashboards
Create a dashboard that is collaborative, informative, and easily customized. In this webinar, you'll learn how to: Add and edit each widget type Surface real-time data from sheets and reports Share dashboards with collaborators Insert live, actionable content like videos, forms, and more
Create Real-Time Data Summaries with Smartsheet Reports
Learn how to report on your work at every level with: Sheet Summaries to define, organize, and understand sheet data Reports to consolidate rows from multiple sheets into a single view Roll-up metrics by grouping and summarizing report data

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Employee Reviews - Pulling & Averaging Category Scores
Hi all, thanks in advance for your assistance! I am working on building a new employee review process for my company and am caught up on one of the final steps. Here's where I am at: I have created an employee review form that individual managers will complete Each employee will have 3-5 reviewers The form submissions will…
Pulling in data from 1 sheet to another isn't working with any function.
PMO Template - I want to pull in the Project Name Column from the Project Metadata sheet into my custom Attendance Sheet > Project Name Column. I've tried VLOOKUP, INDEX MATCH, JOIN COLLECT, I've changed parenthesis to brackets. I am new to Smartsheets and can't get anything to work. I've watch videos, followed community…
Best formula to link data in another cell
Hi all, I wanted to know what the easiest way would be to link data from one sheet to another. I have working cross-reference formulas, cell-link, and even just using miscellaneous formulas to reference them, just as proof of concept. I was wondering, what is the difference between using Index-Match to pull in data, versus…

Formulas and Functions

Discussion List

Help Article Resources