Formulas and Functions

Archives for March 2021

Average percentage of Assessments in a date range
We do progress assessments on a monthly basis and the assessments are given a score as a percentage. I can write the formula to give the average score for all of the assessments on the sheet but I am struggling to write the formula that allows me to find the average score in a date period. The formula I have tried is…
IF ? from name to email-adress
Hi, I have names f.e. "Joerg Schmidt" in one cell and want to automatically change them into their email-address in the neighbour cell. Since it concerns only <10 names it seems a simple task? With the if-formula ? If I would build an general contact sheet could I have a formula like "lookup" ?? Hope you can help ;-) Thx
Can you select individual cells to use in a graph?
I want to use multiple cells from different cells and rows in my spreadsheet. Is there a way to use multiple pieces of data in different rows and columns on my spreadsheet?
hey I am wanting to list the dates say from 20th march and so on down the page in a single column
like so
count rows that contains certain texts in two different columns
Hi Smartsheet, I am struggling with a formula that can solve the following issue: A smartsheet contains multiple columns, two of them (A and B) are texts. I want to count the total number of rows that column A's text contains a text string "XX" OR column B's text contains a text string "YY". Please note, when on a certain…
Displaying a formula column in a form
Hi all I would like to be able to display a lookup column result in a form. The user does not need to select this particular field but when a dropdown is selected in another field, it will lookup a corresponding number if a different sheet and populate the grid behind the form. I just want to display that result on the…
Multiple IF Formula for Matrix
Hello, I'm looking for help on an IF formula which looks at multiple columns to determine a value in a column. The image attached displays the guidelines on how to populate the "Matrix Position" column. The formula will look at the Influence + Impact columns in order to populate the "Matrix Position". I'm stuck. Thank you…
How to reverse text?
Hello, I need to reverse the format of a date stored as text. I used the =(life end date) + "" formula, hoping it would convert exactly as the date column format (YYYY_MM_DD), but nope... it populates as MM-DD-YY. How can I reverse the order? I'm fine with two-digit year, I just really need it to show as year/month/day.…
How to check if a column has a value on a certain row with matching cell?
Hi, I have two sheets, Allocations & Installations. Once an Allocation is approved, the row gets copied to Installation. When the Installation is completed it is marked as such on the same sheet. I would like to automatically have it marked on Allocation as well. They share a [Shop ID] cell. So i need to check from…
How to map data from cells from Smartsheet source to Smartsheet target?
I have a task where I'm collecting information from automation events to a Smartsheet (source). The source Smartsheet contains columns of data of interest. Based on a status in Smartsheet, I want to trigger the copying of data from specific cells in source and paste it into specific cells in a target Smartsheet. The idea…

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 shows UNPARSABLE
I am trying to create a formula, and I keep ending up with unparsable. The name of the field I am accessing is called "Deliverable Number". Now For example, this field contains 2 characters I want to REMOVE Original: DEL #10 - TO Portal Strategy/Solution Desired: DEL 10 TO Portal Strategy/Solution As you can see the "#"…
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…

Formulas and Functions

Discussion List

Help Article Resources