Formula Handbook: Your Ultimate Guide To Master Formula In Smartsheet​​

Formula Handbook: Your ultimate guide to master formula in Smartsheet​

No one can deny that mastering formula will have a massive advantage in your daily work life with SmartSheet, you can efficiently execute calculations and lookups on a single
sheet or across multiple sheets using formulas and functions. You may automate symbols and drop-down fields using formulas as well.
Both manually entered variables and cell references can be utilized to generate a formula. You can apply a consistent computation to an entire column or add formulas to a set of fields. To conserve grid space, you can also create formulas in the Sheet Summary.

Functions like SUM, AVG, and IF are widely used formulas. There are also some unique capabilities proprietary to Smartsheet, including hierarchy functions like CHILDREN. To see all the options, you can look at this Functions List. 

Here are some common formulas you might consider:

Certainly, here’s an expanded list of 20 Smartsheet formulas with examples to give you an idea of their practical applications:

1. **SUM**: Adds up values in a range.
– `=SUM([Budget]1:[Budget]10)`
– Adds up the values from row 1 to row 10 in the “Budget” column.

2. **AVG**: Calculates the average.
– `=AVG([Sales]1:[Sales]5)`
– Calculates the average of sales from row 1 to row 5.

3. **MAX**: Finds the largest number.
– `=MAX([Revenue]1:[Revenue]10)`
– Finds the largest revenue from row 1 to row 10.

4. **MIN**: Finds the smallest number.
– `=MIN([Expenditure]1:[Expenditure]10)`
– Finds the smallest expenditure from row 1 to row 10.

5. **COUNT**: Counts the number of cells with numbers.
– `=COUNT([Inventory]1:[Inventory]10)`
– Counts cells with numbers from row 1 to row 10 in “Inventory”.

6. **TODAY**: Returns the current date.
– `=TODAY()`
– Inserts the current date.

7. **DAYS**: Calculates the number of days between two dates.
– `=DAYS([End Date]1, [Start Date]1)`
– Calculates the days between the start and end date in row 1.

8. **IF**: Checks a condition and returns a value based on the result.
– `=IF([Status]1 = “Completed”, “Yes”, “No”)`
– If the status in row 1 is “Completed,” returns “Yes,” otherwise “No.”

9. **VLOOKUP**: Searches for a value in another sheet or range.
– `=VLOOKUP(1001, {Sheet2 Range 1}, 2, false)`
– Looks for 1001 in Sheet2 and returns the value from the second column.

10. **JOIN**: Joins multiple text strings.
– `=JOIN(“, “, [Column1]1, [Column2]1)`
– Joins the text in row 1 of Column1 and Column2, separated by a comma.

11. **PARENT**: Gets the value of the parent row.
– `=PARENT(ROW())`
– Returns the value of the parent row’s primary column.

12. **RANK**: Ranks a number within a list.
– `=RANK([Score]1, [Score]:[Score], 1)`
– Ranks the score in row 1 within all scores in ascending order.

13. **INDEX**: Gets a value from a specific row and column.
– `=INDEX({Sheet2 Range 1}, 2, 3)`
– Gets the value in the second row and third column from a range in Sheet2.

14. **MATCH**: Finds the position of an item in a list.
– `=MATCH(“Apple”, {Fruits Column}, 0)`
– Finds the position of “Apple” in the “Fruits” column.

15. **INDEX-MATCH**: Combines INDEX and MATCH for complex lookups.
– `=INDEX({Price Column}, MATCH(“Apple”, {Fruit Column}, 0))`
– Finds the price of “Apple” from the “Price” column based on its position in the “Fruit” column.

16. **LEN**: Measures the length of a string.
– `=LEN([Name]1)`
– Returns the length of the string in the “Name” column of row 1.

17. **UPPER**: Converts text to uppercase.
– `=UPPER([Name]1)`
– Converts the name in row 1 to uppercase.

18. **LOWER**: Converts text to lowercase.
– `=LOWER([Name]1)`
– Converts the name in row 1 to lowercase.

19. **AND**: Checks multiple conditions.
– `=AND([Status]1 = “Completed”, [Quality]1 > 90)`
– Checks if the status is “Completed” and quality is above 90 in row 1.

20. **OR**: Checks at least one of multiple conditions.
– `=OR([Status]1 = “Pending”, [Status]1 = “In Progress”)`
– Checks if the status is either “Pending” or “In Progress” in row 1.

These formulas can help you perform a range of tasks, from basic calculations to complex data lookups and text manipulations.

To go deep, you can also use the “Template Set” of Smartsheet, some key benefits are:

  • Learning about using formulas in SmartSheet.
  • Working with live examples of each function in SmartSheet. 
  • Examples of commonly used formulas, such as Red Yellow and Green status calculations Examples of advanced formulas, such as cross-sheet data lookups with multiple criteria. 

How does it work?

You access to the template via this link .

The Power of Next-Gen Project and Portfolio Management

The Power of Next-Gen Project and Portfolio Management PPM Insights 2024 With massive technological and economic disruption,organizations must adapt....

Process Maturity Model

Tipps zur Produktivitätssteigerung

High Performance Teams

Build High Performance Teams Empowering High-Performance Teams With The Overall Team Effectiveness Framework. Contact Us


Work smarter with AI

Forbes Business Council

Press Release: Nico Röpnack Accepted into Forbes Business Council Forbes Business Council is an invitation-only community for successful business...



Scroll to Top