Formulas and Functions

Archives for January 2022

Having overall sheet health based off of parent and child row?
Hi - I know this has been asked and answered similarly, but I am struggling with actually implementing the formulas. I have created a simple sheet that I would like to have a visual R/Y/G/Gr marker at the top of the sheet that is a roll up of the all of the parent rows below. I am basing the formula structure off of this…
Column Name for First Column
I am looking for the column name for the first row that contains the row number. I want to use the column name in one of the workflows. For any other column I know I can use {{Name}}, {{Start Date}} and rename the columns; however, this is not possible for the first column. I work around that I created is to add a column…
Nesting ISBLANK within IF(AND) Statements
Hello, I have successfully established a formula link between two date columns (Start & Finish) and my 3 status trackers (Completed, In Progress, Not Started). The formula I have used successfully is: =IF(AND(Start@row > TODAY(), Finish@row > TODAY()), "Not Started", IF(AND(Start@row < TODAY(), Finish@row > TODAY()), "In…
Countif function for unchecked box
Is there a way to run the countif function if a check box is unchecked? I see a command example for when it is checked, but I want to exclude any checked boxes as those would mess up my counts due to the negative value associate when it is checked.
VLOOKUP separate sheets
I would like reference sheet A zip code with zip codes in Sheet B, and when there is a match change a specific column status in sheet B sheet B status
Setting up an Office Status Indicator
Hello! I am trying to setup a sort of "office status" dashboard for staff. Can anyone guide me on a good way to do this? I am a bit stumped as to how I can get some of the info across. I have one sheet with PTO information: PTO Start (Date) PTO End Date (Date) Currently on PTO (True , False) Name Request Duration (Whole…
Trying to ignore the error cells and calculate average for the column
I've been trying to ignore the cells which have #invalid data type calculate the entire column with numbers. Please see attached. I've tried iferror as well with no luck!! Will highly appreciate if someone can help out!
Nested IF formula resulting in #INVALID DATA TYPE
I am working on a formula to indicate whether a vendor is approved for work and needed to add another condition and a new status. It is working for the most part, but a few rows are pulling #invaliddatatype and I cannot see how to fix it. What I want the formula to say: If the [Agreement Expiration Date] was last year and…
Rollup or Find Average Progress Symbol
I am trying to rollup from another sheet that has the progress displaying as symbols. I created a sheet that rolls up hours, costs, dates, and progress. What would be the correct formula to rollup progress using symbols from another sheet? So, finding the average on progress as a symbol.
Cross Referencing Data Between Sheets
I need to find a function that I can use to simplify some enrollment data reporting. Rather than entering it in manually...every day...it should auto-update based on what enrollment data is entered in another sheet. Looking at the pictures provided...in the ENRL (Experimental) column, I would like it to return the…

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/OR Formula issue
Hi, I'm trying to pull data from another sheet if the date meets the criteria in one of two columns of 12/20/2025. Should I use an if/or or nested ifs? Here is what I have so far but get the #INCORRECT ARGUMENT SET error. Here's what I have so far. Any idea what I'm doing wrong? =IF(OR(INDEX({cutdate}, MATCH([Job ID]@row ,…
Sheet Summary Formula capturing dates before current expiry date column
Hi all, I thought I had this one down but I keep getting an error message. This is a sheet summary formula. Any ideas what I am doing wrong? =COUNTIF([CURRENT EXPIRY]:[CURRENT EXPIRY], >TODAY) Thank you!
Conditional Formatting Query
All the above rules are working except the 4th one in the list which asks for the cell to turn red if Group status is not started and end date is in next 14 days. I have used the Smartsheet AI tool but no luck, I have saved and refreshed. I am using a helper column for my 5th rule in the list which asks the cell to turn…

Formulas and Functions

Discussion List

Help Article Resources