Formulas and Functions

Archives for February 2019

NESTED IF FORMULA WITH PARENT/CHILD ROWS
Hi everyone, I am struggling with a formula to populate a parent row, as either "On Track" or "Off Track". This is dependent on how the child row compares to the "Weekly Goal". The formula I am trying to use is shown below but, Im doing something wrong. (screen shot of sheet also attached) =(IF(CHILDREN([January (Week…
Count Multiple Cells If Multiple Conditions with Date
Hello! I am working on linking a database to a results sheet and I need several metrics pulled out. I have Supplier, Defect Column 1, Defect Column 2, Defect Column 3, and date. What I ultimately need to do is create a January through December count of defects per Supplier. So I have Jan, Feb, Mar, ... Dec. on one axis,…
SUMIF value exists, leave blank if cells are blank (nothing to sum)
I'm trying to figure out how to create a formula to sum a column if there are values, but leave the field blank if there are no values in the range. Is that possible? Here's the background: I'm trying to create a status report that shows whether or not someone has actually entered values into the appropriate column. If…
IF Statement Help
Hello All, I am trying to create a smartsheet to optimize a process. Basically what I need is a statement to recognize if a cell has a specific text value (for example CUP). If the cell contains the value, I need the cell, that I am writing the statement into, to display text (for example Andrej). I tried to use the IF…

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!

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Set Task Duration from Formula
I have a project with many repeated subtasks, and sub-sub tasks. To generalize: Task A has Subtasks 1, 2, 3 Each subtask 1, 2, 3 has sub-sub-tasks a, b, c I would like to describe the duration for Subtask 1 with a, b, c in a general set at the bottom of the sheet. Can I link the duration of Subtask 1, 2, and 3 to the…
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…