Formulas and Functions

Archives for August 2020

Nested If, then, CountIf formula
Hello, I am trying to create a nested If, CountIf formula, wherein the formula refers to a column and only counts status if that column is = to 1 of the 4 options. Then, ideally divide the # returned by the total to return the status over all %. For example if Column: Address is equal to: ABC Street, then Count Status…
Referencing a not blank cell of 5 cells.
Hi, I am trying to populate a cell with data in 1 of 5 cells that are filled from a form. For example, using form logic of the organization that is selected (column Org Name) select the site from that Org (Org 1 and site 1) Each Org has its own list of sites in its respective column. I have tried using IF(ISBLANK(NOT( and…
Current Week/Last Week
I want to be able to get a count of Issues Created, based on certain criteria, for both the current week and the week previous. I tried making things easy for myself and creating helper columns but those didn't seem to function, so I am here. I want to count the number of instances an issue was created if the branch and…
Using #INDEX & #MATCH cross sheet reference formula help
I am attempting to write a formula that says: IF {in sheet# 1} COI Type = "Job" OR IF Agreement Type = "Project" OR IF a row matching the code in sheet #1 matches a code in sheet #2 AND Waiver Type = "Approved-Job Specific" THEN check Approval Required box. The first part of my formula works: =IF(OR([COI Type]@row = "JOB",…
Combining CountIfs statement with Date
I have a CountIfs formula that I'm having trouble getting to work when I add a date condition into it. Basically I'm trying to get a MTD output based on several countifs as well as only counting if an adjecent cell does not contain "Appointment Allowcated". The formula works perfectly when I don't have the IFERROR…
#INVALID DATA TYPE error in formula
Hello. I have a formula which work fine for 2 out of 3 variables, but not the last and I cannot figure out what is wrong. Use case: I want the Waiver Approval Status column to read two approval process columns and return Declined if either row shows 'declined', 'pending' if either row shows 'submitted' and 'approved' if…
Getting countif to work between sheets
I can not get the countif function to work between 2 separate sheets. One of the sheets is my metrics for the pipeline. I want to get a count of how many times a name(account holder) is used on the pipeline and I keep getting the error unparseable or incorrect argument. I try linking the sheets and get the same errors. I…
Reusing the same automation on the same triggered cell only works once
I have a simple automation - If a Status cell changes to red or green then trigger. It works fine unless I try to trigger it again by forcing a change on the cell. I thought it was because cells are locking but I unlocked them and the problem was still there. "Status" is set by checking the values of a group of other…
Turn an online survey to a printed version
Hello, say I created a form (to serve as a survey). I can push this link out, no problem. What if I wanted to make a printed version of that survey, how would I go about doing that? The use case for this is when our customers come in our office and take training. At the end of their training, we'd like to be able to hand…
Auto Populating Formulas
Hi everyone, I have a simple sheet that has data inputted into it from a form. I have the form inputting the new data at the top of the sheet. I have read the following page many times:…

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

Parent Row Status Roll Up
Hello! I'm looking for help on a formula I have yet to find exactly what I am looking for. I have found a couple of others I have been tweaking, but not working exactly how I would like. I'm looking for a formula to roll up the status of children status to the parent row. Here is the breakdown: If all statuses are Not…
Copy all Data from one Column in a sheet to another column
Hi , I'm trying to copy all the information from one Column to another Column in a different smartsheet. Can I use Index for this? I see example of index/match but I don't have anything to match. Example Sheet name "Testing" Has several columns one of which is named "ITEMS" Sheet name "Testing 1" Has several Columns one of…
Can you help me finding my syntax error?
=IF(AND(Statut@row <> "Terminé", ([Date prévue de l'événement@row] <= TODAY(-5), "Rouge")); IF(Statut@row = "Terminé" ,"Vert", "Jaune")) What I want is: If Statut@row is not "Terminé" and the [Date prévue de l'événement@row] is equal or less than 5 days before the event, then "Rouge", If Statut@row is "Terminé", then…

Formulas and Functions

Discussion List

Help Article Resources