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

Calculate Number of Class Sessions between a Start and End Date
I've tried many variations of formulas, but I am getting #INCORRECT ARGUMENT SET every time. [Session (Days)]: Dropdown box, multiselect, days of week (e.g., "Tuesday", "Monday and Wednesday") Session Meeting (Start Date): Date column, enter start date of course (e.g., 2/11/2025) Session Meeting (End Date): Date column,…
IF state with 3 sets of criteria
Hello, I am struggling to get my IF formula to give me anything other than #UNPARSEABLE I am creating an inventory sheet where if "Stock qty" column is less than "Reorder level" qty than to return "REORDERED NEEDED" or if it is greater than reorder level than to put "OK" but the tricky part comes when I add in my third…
Identify matching apps with date conflicts and print out "conflict"
The general idea is I have a sheet where we list each effort and the application it impacts (column "Application") as well as the dates the Code is going to be in my QA environment. I'm trying to create a formula that checks when the "Application" values match and if so compares my date range [code drop date to end prod…

Formulas and Functions

Discussion List

Help Article Resources