Formulas and Functions

Archives for April 2023

Averaging the number of "yes" each month
Too many cell references breaks sheet even after references are deleted
Frustrating that I receive a too many cell reference error even after deleting all references the sheet. Backend must be holding onto the references somehow but this needs to be fixed by Smartsheet. Had this happen before and support says I need to rebuild the sheet.
Find the most recently passed Thursday based on Today's date
I want to count the number of project manager status update sheets that are not updated to the most recently passed Thursday. All the project managers are supposed to update their status update sheets every Thursday. On the following Sunday/Monday, I will check and see which status update sheets are not updated to the most…
Using COUNTIF/FIND function for how many times a name appears in column on separate sheet
I'm trying to build a dynamic formula. The goal is to count how many projects a resource is on for each month and reflect that number in a cell. Ideally it would look at the names from the 'Resource' column and reference if that name is found in the 'Project Team' column (on another sheet). The project team column can/does…
Return a cell value for a row from another sheet
Hello SS Community! Looking to get some help on a formula to return a cell value in a row on another sheet. The sheet below is the one I'm referencing. I'm attempting to retrieve the value in Column "BB" and for Row "Week 1". I've figured how to count the cell by using COUNTIF (=COUNTIFS({week}, =Week@row, {BB}, <>0)), but…
Using dates in sheet summary formulas
Hello, I have a sheet that is pulling data from Microsoft Dynamics CRM using the connector where the date data is not coming across correctly if the column is setup as a date format. I have therefore setup the column in the sheet as a text field. I have a summary formula which uses this column to summarize data by quarter…
IF (AND(MONTH - with payment due date and payment frequency
Hi, I struggle with this formula. Do you have a plan ? I want to calculate the amount depending on the payment frequency, f.e. yearly, half yearly, quarterly, monthly Hope you can help 😃
What's wrong with my formula for setting the Health of a task?
Hello! I'm trying to write a formula that will update the Health symbol of a task based on some of the other columns in my Smartsheet. I think I'm close, but I don't quite have it. I want to set the Health symbol to "Yellow" if the date is within 7 days prior to the Target Completion Date OR if my % Complete column is less…
Networking days taken from a start date and then one of two columns
I'm looking to find the average working days from the start date to the date of the job's first review. The first review could happen in two different ways represented in two different columns, a work in progress review, or an official review. Depending on the complexity of the job they may or may not need a WIP review. I…
How to sync all data entry to another sheet (quickly)?
Hello, I'm looking to have all information from one sheet populate another. Is there an easier way to do this than linking each cell individually? Thank you!

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

How to reference column name pulled from formula
I'm trying to pull a date from columns based on the number in the helper column. I know I can do this with nested IFs, but is there a way to change which column I'm pulling the value from? My column Start Date should be whatever the column is based on the Index. So for the first column below, since Index is 3, it should…
Conditional Formatting Based on 2 Date Columns, with or without Helper Column
Hi! I've scoured the Community posts, and can't find exactly what I need. I am managing a membership list and need to flag when memberships expire before or on a specific date (06/29/2023) so I can contact those members. I have one column with their membership expiration dates, a second column with the 06/29/2023 deadline,…
CRM Templates and Formula for tracking company change
Hello all, Two part question: What success have you had using the Smartsheet CRM templates? We have decent adoption with Smartsheet, and no adoption with salesforce, so are looking to do our CRM in Smartsheet and leverage the data we already have (client info, contracts, etc) Second, I am looking for a way (formula,…

Formulas and Functions

Discussion List

Help Article Resources