Formulas and Functions

Archives for October 2021

COUNTIF with OR statement
Hi! I'm trying to count the number of cells from two different check box columns (cross-sheet reference) which show a check in either one of the columns. I'm getting an UNPARSEABLE error. Could you help me out with this? Thanks!
Adding an IsBlank to an existing formula
Hello, I am using the following formula to determine the expected complete % based on dates in the project sheet. I am setting this up as a template so would like the formula to exist in the column even though the rest of the row data is blank, awaiting the user to populate. Once they populate it, it would automatically…
Using COUNTIFS with AND
Hi! I'm trying to count the number of items which have one status as "Operational" and another status with any option EXCEPT "Operational". This is the formula I have but it is getting an error as UNPARSEABLE. Note that I'm using a cross-sheet reference. Thanks!
Split Text to Column
Hello I have been trying to make this happen but am not quite there yet. I have the following column that contains CAH- Moraine. I am only trying to pull Moraine into a new column. The formula below is not quite right. Anyone have any ideas? =RIGHT([RECIPIENT COMPANY]@row, FIND("- ")) Thank you!
How to change date modified update to be triggered at the row level?
Hi All, I found a formula in the forum to calculate duration of time in days, hours and minutes and it works flawlessly.... except. The issue is after all of the searching for a solution if I add a new row, hit save, modify anything on the sheet it updates the entire modified by column. Notice both rows have the exact date…
=COUNTIF(CHILDREN(), 1) + " of " + COUNT(CHILDREN()) Formula help!
Currently using =COUNTIF(CHILDREN(), 1) + " of " + COUNT(CHILDREN()) with check-boxes as a quick look to tell how many deliverables have currently been accounted for. I need to add additional lines under the parent row, but don't want them to count toward the total shown at the top. Is there a way to do this?
How to trigger notifications for collaborators when responding to questions entered Smartsheet?
Nested IF statement
I am trying to allow the % Complete column update the Status column. My Status column has 3 choices, although I've unchecked the box requiring the use of a dropdown choice only. This is the formula that I've written, but it returns the error "Unparseable". Any thoughts would be appreciated! =IF([% Complete]@row = 1,…
Looking for Matches
I have two columns of data. One column I know is correct, the other has the same information but is a manual entry with possible human error. I am looking for a way to compare the two cells to see if the info matches. For instance the first column may have 220656 and the column next to it may have 220655. I need a way to…
Issues with CONTAINS using Cross Sheet Reference
Hello, I've created a COUNTIFS formula referencing another sheet. Each section of the COUNTIFS references the same sheet so that isn't the issue I believe. Everything works up until the CONTAINS portion. Is there an error with my syntax? =COUNTIFS({Status}, New1, {GSP Category}, "New GSP Onboarding", CONTAINS("Onboarding",…

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

Index-Collect
I have a feature column, a task (multiple tasks per feature) column, a task date column and a feature date column. The task dates can be of three different types. They can be all dates. They can be dates and "DFD + Date" (example: DFD 3/10/2025) TBDs Once the feature, task and task date columns are filled, I want to…
Formula Order of Operations Problem
I am trying to use a formula with CHAR(10) between each missing asset item to build a nice list to use in a record search return automation. I am using one row for each employee with multiple uniform items in several columns. My formula does well until it finds an item in the formula sequence is not missing, then it quits…
Partial Formula Working
Hello, Everyone. I have a commission sheet with many columns, and I have a 3 part formula to calculate commission based on 3 different % depending on which month of the contract the sales team is in. The first two IF statements work beautifully. The 3rd part I need to calculate that if the Month of Contract is greater than…

Formulas and Functions

Discussion List

Help Article Resources