Formulas and Functions

Archives for September 2023

Display the prior "Monday" date from the following week
A buyer asked that I create a sheet with a column to display the prior Monday "date" of a following week. The goal is to set an automation triggered by the date in the "Reminder Date" column, to call the vendor on Monday to confirm the goods will be or have been shipped. I thought I would ask if this is possible before I…
is there a column number function for vlookup?
VLOOKUP requires a number for the column position in the table reference. But I have users who have been known to insert columns into the referenced sheet to the left of the needed column, throwing off the returned value. Is there any function I could nest inside the vlookup where I could provide the column *name* and…
Max Date - Collect for the last entry
I am not even sure where to start today.. I want to collect the last date for the entry in the Solution #31 column. But this column should only have entries once per week (amongst 30 plus entries on the other items on the sheet). Thanks!
How do I conditionally format a cell to turn multiple colors for different conditions?
I want a cell to turn yellow if it equals $0.00 and red if it's less than $0.00, I've tried to add a new rule but it doesn't apply the rule for the less than $0.00 condition. What am I doing incorrectly?
AVG formula and Invalid Data Error
Hello, I seem to have hit a bit of a pickle (or an afternoon brain fart) while trying to create a roll up. Essentially what I'm trying to do is create a roll up of averages by month and my formula is returning an Invalid Data Error. I checked the reference columns and they are indeed date columns but beyond that, it should…
Formula Help for
I am trying to make a row turn red if both the Interview date AND Time Slot are the same. I was able to do this with just Interview dates being dups but am getting stuck with how to also add Time Slot into the mix. I used a helper column to with the formula =COUNTIF([Interview Date]:[Interview Date], [Interview Date]@row)…
COUNTIFS with OR and HAS - #INVALID DATA TYPE error
I am trying to count the number of decisions where the impacted workstream is either Enrollment or T&R and status is requires leadership attention. The workstream field is multi choice so a decision can impact one or the other of the workstreams or both, so need to ensure not double counting which is why I need to use OR.…
Combined Match Values with cross-sheet matching
Hello, I am attempting to match to two different sheets. Essentially if the linked ticket column matches the risk register ID I want to use the first formula and if it matches the BRD (second formula) I want it to use that formula. I am attempting to combine these into what I would assume would be an if statement but…
Is there a formula to extract the state from an address?
Hi!! Is there a formula to extract the state from the following address? The state should be after the comma and before the zip code number. 12345 Paris Rd Dallas, Texas 75240 United States Validation Status: Validated 9856 SLEEPING BEAR RD SKOKIE, Illinois 60076-1920 COOK United States Validation Status: Validated 333 E…
Adding text before a Sum
Hello I have a basic formula = column, minus column. These are monetary values so I want the result to be prefixed with a £ but I can't seem to work out how to do it. I've tried ="£"&SUM(....... but to no avail. Any suggestions? Thank you Katie

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

Getting Unparseable error using IF function
Hello, I am trying to use the IF formula to perform a date calculation based on a value selected in another column but keep getting UNPARSEABLE error. Here is the formula that I have used. I have also pasted the screenshot for reference. Can anyone help me out on this? =IF([SLA]@row = "Standard",[Feasibility_Start…
Automation to Send Notification to Shared Mailbox
I am trying to send a notification to a shared mailbox, when a row is added (from a form), with certain criteria within one of the fields on the row. Ideally this row would contain the information from the submission (similar to what is sent when submitter selects 'Send me a copy of my responses'). If this is possible,…
SUMIF cell values land within a given week
Hey, I've gotten some fair formula experience over the last year (mostly through googling and this searching this forum) but hit a brick wall with my latest ambition… I have a smartsheet in Gantt mode tracking events and sizes relating to the event. I'd like to be able to use a date picker in the summary tab to pick a…

Formulas and Functions

Discussion List

Help Article Resources