Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template to view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.

Product questions?
Ask it here! The community's got your back.

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

how to use "<>N/A" formula in smartsheet Date column
hi, using countif() formula for count total count of "<>N/A" cells. when use formula =COUNTIF({Master Tax Calendar Range 3}, "<>N/A") and my count is zero. my column format is Date. same formula count correctly in excel but not working in smartsheet. please suggest formula for how to count total, if my date column contain…
Weighted Sum of Checkboxes
I'm looking for a method of creating a "score" based off the sum of checkboxes in a row. Various columns ideally would have different weights, creating either a numerical sum or a percentage out of 100. I'm stuck at having a checkbox generating a value to sum. Thanks.
How can generate a weekly report using formula and without using a Column Helper?
Hi, Please help me on writing a formula to calculate the weekly sales data. Please see the attached screenshot of my example data.
Update Request Results showing data that was deleted from Update Req. Form
I am sending an Update Request (UR) form to employees to have them tell me how many hours they worked each day. The smartsheet is prefilled with 8 hours each day. So when the UR form get to the employee, they just make adjustments as needed. In the image below, the employee received the UR form. Deleted 8 from the Hours…
formula answer from decimal to year, month
Hi I need to a formula to convert an answer that i get from a formula to a year, month format Formula I am using =(TODAY() - DATEONLY([Appointment Start Date]2)) / 365 = 2.27397 Convert the 2.27397 to Year, Month
Average If formula
Looking for help with an average function if anyone can help, we have a column with a company name (SI Team) and a column for response time (this gives number of days). Looking help with a formula to count up and average all the response times IF the company name is ABC. Help is greatly appreciated. I think it might need…
Help with Countifs - Summary Sheet
I am wanting to have a shared user be able to input a Startdate and EndDate which is then calculated using Countifs. Is this possible in a Summary Sheet? I get an unparseable error for the code below. Any suggestions? Thanks. c
I can't figure out what's wrong with my formula. I keep getting an "#Unparseable" Error. Help?
Can someone help me figure out why my formula keeps giving me an error? I can't for the life of me figure it out: =IF(AND([8p LTC Peak]2 < $[8p LTC Peak]$1 + Green#, [8p LTC Peak]2 > 0), "Green", IF(AND([8p LTC Peak]2 < $[8p LTC Peak]$1 + [Blue]#, [8p LTC Peak]2 > $[8p LTC Peak]$1), "Blue”))
RYG Stoplight Formula Looking at Dates, Status and % Complete
Hi there SS Community! So I tried to see if I could find something to help me with my formula for having the RYG stoplights populate based on data captured in both the Status and % Complete columns. I started with a basic formula but I wanted to try to also include the Start and End Dates. Here are my outcomes: (1) If the…
How to count projects by status and date range
Hi, I'm trying to count the number projects based 2 criteria: status and date range. Both of criteria are located in separate raw sheet I have been getting #unparseable #invalid operation. Below is sample formula used, basically I'm trying to count projects in year 2020 that have "status" complete: COUNTIFS({Integration…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Using JOIN/COLLECT and an OR?
I'm trying to write a formula using the JOIN and COLLECT options. However, I've hit a sticky point when I want to include an OR criterion on the COLLECT portion. My current formula (which works perfectly) is: =JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, "Live"), ", ") However, I want to…
INDEX/COLLECT with both MATCH and CONTAINS criteria
I am looking for help with a cross-sheet INDEX/COLLECT formula. I need to MATCH in one criteria and CONTAINS in another criteria. I can get them working separately but I don't know how to combine into a working formula. Source Sheet: Destination Sheet: In the Attending School column in Destination Sheet, I'd like to…
Parent Color Symbol, Based on "Average" of Child Symbols
Okay, here I am again! On the Project I am working on, the Stakeholder wants parent rows for no reason other than to be able to expand and collapse the data. I nicely suggested that since the data would only be for 1 year (broken down in quarters and about 5 users listed each quarter), that since the data was not excessive…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags