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!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

Watch video

Trending in Formulas and Functions

If then formula help
Hi All! I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the initiative’s average score is between: 5 and 9 it is considered a project and if the score is between 10 and 15, it is considered a program. I…
Dashboard Last Updated Date
I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet. I don't think I can do a formula in the date field type. Any other suggestions?
Counting yes and yes w/notes
Hello, I am having trouble counting using the COUNTIFS formula to yes's and another category called "Yes w/notes". Here is the current formula that I have: • =COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, @cell = "Yes") / COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is…

Formulas and Functions

Discussion List

Help Article Resources