Formulas and Functions

Archives for November 2019

countifs + find formula with multiple criteria
Hello, I'm fairly new to smartsheets and trying to solve a roadblock. I'm trying to count the amount of times a specific string is in a cell with two types of status: closed and submitted. I'm using the FIND because some cells contain more than one ticket # (i.e. "Ticket-432, Ticket 12435") =COUNTIFS({Database Range 1},…
Automatic Move Row and External Sheet References
I am having issues with the Automatic move row function and external sheet references. It doesnt seem that the automation will trigger if the formula needs to look up a value from a different sheet. Proofing is not turn on for this sheet. See screenshot for the formula Automation: If cell= "a number", then move to another…
Formula help for previous month
I have a helper column for a report that I want to check the box if the DATE cell is in the previous month. Any help is appreciated!!
Date formulas - Months
Hi - I'm trying to add 6 months to the day to a date referenced in another field. It is basically a date of execution and plus 6 months a review date. How is this possible? Adding the number of days gets me close, but not exact. Thanks!
Formula Help
I'm trying to calculate how many days the process takes. I want to calculate the days from "Date Request Received (LAW)" to "Date of City Council Decision". If "Date of City Council Decision" is blank, I'd like it to use today's date. I can't figure out where I went wrong. Thanks in advance! =IF(ISBLANK([Date of City…
Colour grading
Hi all, Is there any way that i could, based simply on ascending order of numbers, colour code a full column, as i have in the picture below? Thanks.
Attempting to convert data from check boxes to Multi Select Dropdown
Thank you in advance for any help you can provide. Since the Multi Select Dropdown option was not around when we created our form, we used a variety of check boxes to gather the required data via a form published on our website. Now that the Multi Select Dropdown is an option, we would like to migrate that data over to a…
IF formula with allowable variance
I need to write a formula that will put Yes in the column if the Quote and Invoice values are within $5 of each other, or if the Quote Frequency is recurring. Here is what I have so far, but I can't figure out how to add the allowable variance of $5 =IF(OR([Quote Amount]13 = [Invoice Amount]13, [Quote Frequency]13 =…
HELP writing a multi-variable IF Formula
Looking for some help with automating a Health column with Red, Yellow, Gray, Green balls based on 4 corresponding values in a Status drop-down. But there are also some overrides that I want to incorporate and can't figure out. Back Log = Gray In Progress = Yellow Flagged = Red Complete = Yellow Override 1 Marking…
Material Tracking
Please help haha It is difficult to put into words exactly what I am trying to accomplish, but I'll do my best. Please ask questions if I'm not being clear enough. Example: I have a material column for material that is being delivered to a job site (Sheet name is Delivery Tracker). Each cell in the column is an individual…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

If cell is blank
=IF([Audit Due]@row < TODAY(), 1, 0) if the Audit due is blank I want the the answer to be 0. How would I write this formula?
If Statement with Multiple Factors
Hello, Sorry for needing additional formula help. For the HTN Kit Helper, I need to it either display Blood Pressure Kit with the Educational Language Helper, except if BP cuff helper is Extra Large Cuff I need it to only display "Extra Large Cuff" in the HTN Kit Helper column =IF([CK Type Helper]@row = "Blood Pressure…
SUMIFS with multiple OR criteria??
Hi Experts. I need to do some $$ calculations. I am working on a summary page I need the sum of the column Funding Committed, ignoring the rows where Pre-Approved or Declined are checked (they move the calc to another column). Something like: =sumifs({Committed:Committed}, {Pre-Approved:PreApproved},1, OR…

Formulas and Functions

Discussion List

Help Article Resources