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 do you add 2 SUMIFS in same function or SUM of Range based on 2 values in SUMIFS Criteria Range?
I have a schedule that has locations in different regions and a install status picklist. I need to create a sheet summary function that calculates a numerical value from one column based on the region and the status. I have two SUMIFS formulas that work separately but I can not add them together nor can I get the logic of…
Can I have a dropdown that has a formula AND dropdown values can be selected?
I have a dropdown (Status) that I want to be automatically updated based on another field (% Complete). I have the formula written and added to the Status column. BUT, I can't manually select/override the Status dropdown. Is there a way to do both?
Nesting IF statements with the FIND function
Hello all, I am having an issue nesting an IF statement I created for a specific type of sheet. Lets say we have a column named Full Name and it has values in it like "This Long Name - A new value" The original function would be something like: =IF(find("This Long Name",[Full Name]@row)=1, "TLN"+MID([Full Name]@row,…
Formula to show how many tasks are occuring during a time frame.
I have a list of about 60 projects with start dates and end dates. Project name is in the primary column then start date and end date are the other two columns. I want to see how many projects are occurring during each month. This is the formula I have so far but I know it isn't right. I want to have a formula that shows…
Dynamic Sheet References?
Is it possible to build a sheet reference dynamically using a link (the sheet link or the sheet ID) that is pasted elsewhere on the row? I am a specialized resource and use smartsheet to help prioritize my own tasks and add a bunch of extra data that I track. For my tasks that originate from customer-facing projects, my…
How do you get a field on a form to auto-populate based on the selection of another field?
I've created a Smartsheet with all of the appropriate fields and have certain columns pre-populated with specific data. When I have people fill out the form, I would like them to choose a location & then have the corresponding address from the Smartsheet auto-populate based on that selection.
Date calculation formula not working
Two date columns Last Service and Next Service I want "Next Service" date to be "Last Service" date plus one year. I created the following formula in the "Next Service" column: =IF([Last Service]@row <> "", DATE(YEAR([Last Service]1) + 1, MONTH([Last Service]1), DAY([Last Service]1)), "") It correctly calculates the Next…
Improving Formula Descriptions
I feel like it could be better shown, for a variety of examples, how formulas can be used. For example, this image doesn't really tell you what the answer to the example is. The example only shows that those data types can be used but doesn't help you understand what it does. Minor gripe I suppose. Also sometimes I wonder…
Updating a status for new form entries based on approval
Hi all. I need help please. I have a form that generates new entries on a list. They are set to default to a "Requested" status when they arrive on the sheet. Each entry is set to go through an approval workflow ending with a Senior Manager Approval. There are a number of columns but the pertinent ones would be: Status…
Checkbox Formula
I am struggling to use a formula within a checkbox column. I was able to complete the first function of finding the % complete of Children Checkboxes using this =VALUE(COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN())) * 100 + "%" However, now I need to find the % complete of those total values. (So the totals of grandchildren…

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

Netdays calculation with optionnal date column
Hi i'm looking for a formula to calculate the netdays between 2 dates. but i have 3 dates in the equation. a date column (date 1) whit references from another sheet and a manual one (date 2) to overwrite the first one. the third date is the "today" date (date 3). so i'm looking for something like: netdays between date 1…
Issues with Numbering Children (Child 1, Child 2, etc.)
Hi! I am trying to implement a column formula in Smartsheet that assigns an ordinal number to each of the children in a sheet. For example, the first child would get a "1", the second child a "2", and so on. I've tried several options, and the one I liked the most is the following: Create an auxiliary column with a "1"…
Index/Collect from Descendants
In this sheet, I have a column [Groups] which is a parent column to multiple "Groups", each of which is a parent column itself to tasks associated with each group. I need help putting a formula in [Start Date] of the parent row that would give me the very 1st value from [Date] column that is a Descendant of [Groups]@row…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags