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.

Converting text to date via formula
Good afternoon. I have created a sheet with a form to capture reservation details for a live performance. There are 4 performance date options and form users identify their 1st-4th choices. Once received, they are assigned a date and the cell in the appropriate "Confirmed" column is marked YES. There are 4 "Confirmed"…
Building a red ledger
My apologies I am not even sure how to search of this, but I am trying to build a red ledger. Basically have it set up so there is a required sale every day and anything under, shows negative, anything over just shows zero. So I have 1 column that has the number sold, next is sum of column1 - 5000, the last is column2/100.…
Days Remaining Calculation
How do I set a date in the future and then count down the remaining days until that date? For example, my future date is 8/17/18 and each day I want it to show how many days are remaining until 8/17/18.
index match formula not working
Hi, I am using the below index match formula which is not working. The MFG Rev 18 area range is a "contact list" column type, and the column used for the formula is a Text type. I assume the formula is not working due to the type, is there another way around this? =INDEX({Mfg Rev 18 area}, MATCH([Start Date]3, {start date…
Trying to do a group IF statment
Not sure how to ask this but I am trying to set up a tracking sheet that will automatically pull info from a master sheet. I would like to send out a form from for someone to fill out and all they would need to do is put in the job # into the form, and on the track sheet it will automatically fill in the rest of the…
Link if?
Hello! Is there a way to link cells, or even just copy cell/row info, if a specific criteria is met? I am new to Smartsheet and haven't been able to find anything to do this. This is the situation: I am using a webform to create Purchase requests. I have approvals set up. Once the request has been APPROVED, I want specific…
using OR in a formula
Hello Smartsheet Community, I'm looking for some help in utilizing the OR statement within a function which references columns in another sheet. I am trying to count how many rows have a request status of either "Open", "Awaiting Addl Info", or "In Progress" from specific departments who submit these requests. I seem to be…
Dates - Gantt Chart
I'm building out a plan with tasks and dependencies and want the dates to populate based off a vlookup. For example, If "Sprint 12" is selected then the proper start date is set in the Sprint Start Date column...which I am doing now with =VLOOKUP([Sprint Start]4, $[Sprint Start]$123:$[Sprint End Date]$136, 3, false). The…
Counting # of checked boxes in one column
Hi all, I've tried a few different formulas but can't figure out how to count the # of checked boxes in a column. My column is labeled "Approved" and has 131 cells, so I tried: =COUNTIF(approved1:approved131, 1) But I'm getting #BOOLEAN EXPECTED
Bug: COUNT({invalid ref}) returns 1
Unintended feature; 1. I create a new sheet. 2. I add this formula to any cell: =COUNT({blah blah blah}) The function returns 1 (reference is not valid) Other* functions return #INVALID REF as expected. I can see WHY it would return 1 (it has one thing), but it might be better to return #INVALID REF instead. Craig *I did…

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

Get a Countif on values in a multi-select column of cells
I am trying to write summary formulas to determine how many values appear of each in the drop-down of a column. I tried adding a wildcard, I have tried HAS, and tried CONTAIN, but I'm still not getting the right values. Currently I am getting 0 as a return value on this formula: =COUNTIF([ROI Type]:[ROI Type], HAS([ROI…
summaries in report
Hi i'm trying to build a report with summaries options. i have two column that i want a Min and Max summary. the column for the Min i a texte column and works well. but the other column has formulas and the Max doesn't works. there is a way to fix that? (instead of creating another text column in the source sheets?) here…
Converting email addresses with hyphenated First and/or Last Names
I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one. I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags