New Sheet Formulas That Enhance Your Productivity
by Kara Lumley
Each year, Smartsheet hosts a hackathon called Hack the Sheet. During this hectic, exhilarating, and productive week, our entire product and engineering teams get to work on projects they are super passionate about. We divide ourselves into teams based on interest, have some late nights, and are judged by peer and leadership panels.
My team worked to build a number of new formulas, all highly requested by Smartsheet customers, that we believed would significantly improve user productivity. We set a goal of creating six new formula functions by the end of the hackathon. We ended up building 24!
As many of these functions were on the wishlists of our most enthusiastic customers, we know they will help teams in every industry to be even more effective while working in sheets. If you want to see all of the new formulas in action, check out the Smartsheet Formulas Example sheet. Here are the four most-requested formulas, that also happen to be our favorites:
=IF(CONTAINS("shirt", [Clothing Item]:[Clothing Item]),"True", "False")
CONTAINS enables you to search for a character or string in a cell or cell range. It returns true if found, false if not found.
You can use CONTAINS if you need to know if there is a specific word, character, or an anomaly in the data. Just remember to put quotes around the string you are searching for, then the formula will search in text/number, dropdown, contact, or symbol columns (search for the underlying symbol name, like “red”).
=COUNT(DISTINCT([Favorite Fruit]1:[Favorite Fruit]10))
DISTINCT returns the distinct, non-duplicate values and is used with another function.
For example, you could use this formula if you sent out a survey via a Smartsheet form and want to count how many unique values were provided.
=AVERAGEIF([Transaction Total]:[Transaction Total], >900)
AVERAGEIF returns the average of cells that meet a given criterion. In the above example syntax, the formula would look at the entire column, but only average the numbers above 900.
Used within another function to reference all descendant rows of the referenced parent row, DESCENDANTS will look down all children, grandchildren, great-grandchildren and so on from the referenced parent row.
Coming Soon: A New Way to Assign Work
Formulas like =VLOOKUP enable your sheets to automatically return key data based on its association with a unique identifier. Today, formula output cannot be in a contact column, but we plan on supporting this very soon.
What does this mean for you? Teams that field requests in IT, marketing, and other departments will be able to automatically assign incoming requests to the correct person. This will help eliminate the manual process of assigning new work, and get the right people working on the right things more quickly.
For example, if you are a West Coast regional manager for a consulting firm, you can create a sheet for tracking customers. Using a Smartsheet form, prospective customers can fill out their name and address to request a bid.
With the forthcoming capability, you’ll be able to quickly set up a formula in the contact column then, dependent on the submission, look up the state and which regional manager is responsible. The correct person is assigned to that customer, and a notification is generated to let them know they have a new opportunity to work — all without any manual input.
One of Smartsheet’s fundamental commitments to our customers is continuous improvement. Ensuring that you can move from idea to impact — fast — is critical. Be on the lookout for performance enhancements that will improve formula speed within your sheets. We’re happy to share these changes with you, and look forward to many more to come.
For the complete list of new formulas, check out our updated Smartsheet Formulas Example template in Solution Center, and select the filter “Formulas added in July 2019”.