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.

See Current Sheet References and Delete them
I have a sheet i'm building and I have run into the maximum number of sheet references. This isn't a big deal as I've just made another sheet, and i'm going to use a report to conglomerate them, but I found out that I cannot go through my references and delete some of them, or even see a list of the references made. Is…
Parts of a whole inventory suggestions
Hello! we use Smartsheet mostly for inventory management and I was wondering if anyone had some suggestions on how to write a formula to automatically subtract the parts that go into each item while also subtracting the completed part. I currently link in the part inventory from one sheet and update it there so that it…
Report filter not working properly
Hi, I'm not sure if there is an inherent filter mechanism as part of Smart Sheet reports but I am trying to use the report filter function to only show rows for specific information. Column 1 - list of products (e.g. apple, orange, grapes .. etc) Column 2 - list of groups that buys the product (e.g. group 1, group 2, group…
time analysis via modified columns for automated alerts
I'm trying to find a pattern as to how the numbers are generated when two modified by cells are subtracted from each other. At first I thought it was in minutes and separated via location in the number, but upon testing that proved to be incorrect. Does anyone know how to translate the number that is given when two…
Dates Less than 1 duration
Trying to get a formula to calculate less than day if duration is less than 1 whole day. Example - Start date is 7/30/2018 End date is 7/30/2018 Duration is .12d Formula Variable Date is 8/14/2018 If I want to run a formula to calculate the days from Variable date less end date - I get 15. I am looking for something more…
vlookup not updating
Has anyone had a problem with vlookup not updating? I had a situation this morning where vlookup was not refreshing the correct value when the search value changed. The way I found to fix it was to re-type the entire formula.
Zapier sends string, but Smartsheet processes as an integer
We use a unique number sequence by combining two integers for each row sent to Smartsheet. I worked with Zapier to verify that the integers are sent to Smartsheet as a string, not a number. What I am seeing is if the string is not long enough, Smartsheet converts the information to a number and our formulas do not…
formula referencing cell with formula
Hi folks. I'll do my best in describing my issue. Trying to create a formula that multiples 2 columns together however getting #INVALID OPERATION error. Does it have to do with a column that is actually a calculated formula? Column A: "Effective Time" is a calculated column based on this formula (shortened for this…
Avg formula % complete based on a specific end date
Hello, I would like to know if it is possible to create a formula that calculate the average % complete for all tasks that are ending at a specific date (important milestone) in my schedule. The schedule I have have a lot of tasks going all the way to 2020 but I want to track only those that must be completed by Nov 2018.…
SUMIF formula is making me crazy.
Just when I think I am getting better with formulas I hit a wall with something that seems simple. I need to add 2 column sums together for the rows that have a RED ball Status. =SUMIF(Status:Status, "Red", [Repair Inv. Amount]:[Repair Inv. Amount] + [Diagnostic Inv. Amount]:[Diagnostic Inv. Amount]) Then I get an invalid…

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

I have the Year and Month on a sheet. How can I have the summary look at both to count?
I would like to create a summary if the month is Sept AND the year is 2024 to count. I have found that I can use the and but keep getting an error message This is the Formula I have in the Summary that is coming up #Unparseable =COUNTIFS([Next Renewal Year]:[Next Renewal Year], "2024") AND(([Next Renewal Month]], "Sept")
Formula Help
Hi All - I am hoping someone might be able to help me. I am terrible at formulas and I cant seem to figure out how to pull what I need on my own so I copied a formula from another sheet we have at our company. The issue is that it returns it multiple times. So the expected result is "four", the formula returns "four"…
Are You Able to Join Two Columns into a Data Shuttle Dynamic Dropdown?
Hi everyone, I'm hoping someone here can help me out with a challenge I'm facing in Smartsheet's Data Shuttle. I've been trying to use expressions to combine two columns into a single dynamic dropdown column in a target sheet, without changing the source sheet. For example, I want to combine Num and Name into a format like…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags