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.

Change just once a formula used several times
Hello, I have a sheet that it is a summary of several sheets and I have a lot of formulas that use date like "<=DATE(2020; 9; 4))" and I want to change that part of the formula to for example "<=DATE(2020; 9; 6))" and just do it once because I have that several several times. Is there any way to do it? If I try to search…
Sumifs by Date range...
I need a Formula that will sum all the Income from various projects that were completed in a specific range of dates. I am haven't been able to make one up or find one online that has worked.... Any help would be Awesome :)
Calculating commissions
Hi There, I am trying to put in a formula to calculate expected commission as a percentage of dollar amount but I am getting #UNPARSEABLE error Formula as follows =[Loan Amount]3*.77% Any help would be greatly appreciated. Gary
Using a Relative Date in COUNTIFS Formula
I'm looking to use a relative date in my COUNTIFS formula (ex: In the next 7 days). This needs to be evergreen so I don't have to be updating the dates every day to have an accurate count so I need a relative date functionality rather than using a lesser than-greater than formula.
COLLECT and MAX
I have been trying to find the right combination of formulas to do this in place. I am trying to report the name of a contact in the same row as a max value. I have attached an example photo to help describe my situation In the scenario, I am trying to look up the maximum number in the Parts Column and return the managers…
average(children()) not working when cell in child row is a calculated field
Hi, =Avg(Children()) not working when cell in child row is a calculated field as shown above. Is there anyway to fix this.
Parent status (RYGB ball) changing based on child status (dropdown)
I am looking for help with a formula where I have a parent row and then multiple children rows below. The Parent has an overall status that is a 4 ball symbol of red, yellow, green, blue) My child rows are using a dropdown menu called "Confidence" with percentages as the values (10%, 20%...100%). I'd like to set the the…
How do I have a Dashboard show only 100% not 150%
Please see below example. I want to only show 100% as the top, not 150%. Is that something on the sheet itself that I need to do? If so, I only have a % Complete and % Remaining columns. What would i need to place in there to fix this.
Is there a way to have the grid view colors migrate to the calendar?
In the vacation planner template, the grid rows and the calendar rows are matched by color (the person's name in the calendar) for the first 4 rows+child rows. The calendar matches those colors. When I try to do this myself, the names/rows in the calendar are different colors or even the same light blue color. Is there a…
Help with DISTINCT Function
I need to create a list of unique names pulled from a column on a different sheet. I used this example to get things generally working. However, I'm not ending up with a truly unique set of names. The formula in question is: =JOIN(DISTINCT(COLLECT({Fruit}, {Fruit}, @cell <> "")), ",") + "," Here's an example of how my…

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