or Explore Discussions

# Formulas and Functions

Let the community help you with that tricky formula or function! See how others are utilizing the power of formulas and functions to save them time and create more powerful solutions with Smartsheet.

## Discussion List

• Complex formulas ammendment
In Formulas and Functions
Hi All, I have an existing Smartsheet that has some relatively complex formulas to count the the status of an action as there are several conditions for an action to show as &quot;Open&quot; and &quot;Overdue&quot;. Over the last few days,…
Accepted
21 5
• VLOOKUP Doesn't Appear To Work With Auto-Number Columns/Cells
In Formulas and Functions
I have a main sheet containing a list of projects with the first column containing the ID of the project which is configured as an auto-number column. I am working on setting up another sheet to track projects tasks and want to be able to …
31 1
Answered - Pending Review 31 views 1 comment Most recent by Andrée Starå
• Nested IF AND OR
In Formulas and Functions
I am trying to get a formula for a check box that combines an if AND with an If OR. I have a column labeled Rental Billed that is a check box. There is another column that has multiple status options from a drop down. I want the formula…
41 3
Answered - Pending Review 41 views 3 comments Most recent by Sean Morgan
• COUNTIF Help
In Formulas and Functions
Hello, Is there a way to count the number of times a contact appears in a certain column, if there are multiple contacts in some of the cells? (Image) For example, I can use COUNTIF to see how many times Charles appears in cells where it i…
11 1
Answered - Pending Review 11 views 1 comment Most recent by Andrée Starå
• The 25,000 cell reference limit is puzzling
In Formulas and Functions
Dear Smartsheet: I am a bit confused by the 25,000 cell reference limit.  In my specific case I am simply trying to VLOOKUP from a table where I have 700 rows and 17 columns.  That equals 11,900 cells which is less than 25,000 cells.  So…
644 views 22 comments Most recent by Paul Newcome
• Manual % complete formula in the parent row
In Formulas and Functions
I am not using % complete automation in the parent row as some sub tasks do not have dates. However, using =AVG(CHILDREN()) in the parent row I get an incorrect % complete. The actual %complete should be 26%. Do I need to use a different f…
Accepted
52 5
• Multiple IF/AND for duration and status
In Formulas and Functions
Hi Community, I am trying to structure an IF/AND formula to reflect the following conditions: IF [Duration]@row &lt;&gt; 0, AND IF [Complete]@row =0, &quot;Not Started&quot; IF [Duration]@row &lt;&gt; 0, AND IF [Complete]@row =0, AND IF[Co…
Accepted
21 2
• Parent cell - Status symbol total
In Formulas and Functions
(Image) Hi there, How can the parent cell (circled in blue) automatically reflect the correct color/symbol concerning the tasks below? When I say automatically, I refer to do it by default, not me selecting the symbol all the time. Can th…
21 1
Answered - Pending Review 21 views 1 comment Most recent by Ryan Nix
• Create a Duplicate Flag
In Formulas and Functions
Hello, I am trying to create a flag that turns red when a duplicate name is entered. Here is the formula I have so far, but it keeps telling me it is &quot;#Unparseable&quot; =IF(COUNTIF([Client/Resident Name]:[Client/Resident Name]@cell)…
Accepted
21 1
Accepted Answer 21 views 1 comment Most recent by Maria Watters
• COUNTIFS Formula
In Formulas and Functions
I am fairly new to working with formulas. I have a Project Status column that contains several values with &quot;In Progress&quot;. For example, In Progress - Scoping, In Progress - Development, etc. I would like to count all the instances…
Accepted
11 3