Formulas and Functions

Archives for November 2019

Trying to calculate week start using countif but the calculation seems to be wrong
I'm trying to calculate count of dates using the formula CountIF([date]:[date],"11/25/2019") Wherever i have the date as 11/25/2019, it should give me the count, it should give me the total count. But the formula isn't giving any data using this formula
Identifying current week for milestone spanning several weeks
Hi community, I'm looking for help on identifying milestone tasks that span multiple weeks (see screenshots below). Our project plans have a "weeknumber" helper that checks the END date of a milestone task to set the week number. I then use an Index/Match in the Summary Sheet area to identify the Milestone task name for…
sum hours from a column, if grandchild row in another column has specific text
So I've been tasked with creating a timesheet for our consultant agency by quarter, so each sheet will cover three months, why it has to be in Smartsheet I'm not sure. Context Each day, employees have three types of projects they can be working on: "Proj1", "Proj2", "Proj3". I need a formula that will add up all the…
Do not want Cells with "N/A" figured in my formula
=SUM(CHILDREN()) / (COUNT(CHILDREN()) + COUNTIF(CHILDREN(), "")) I am using the above formula for percent complete in my project plan as seen by my picture. My question is when certain parts of the project have not been started yet how do you exclude the cell with the "N/A" value in it from being calculated?
Help with a formula (match? index? vlookup? if/then?)
Hello! I am looking for help in building a formula for a sheet. I create semester reports on a sub-group of persons that we track in a master database. Because this database is extensive (multiple years) but my reports are created from scratch a few times a year, the easiest way to create mine is to pull the IDs of the…
Formula to flag red with multiple criteria
Hi. My name is Frank. I have been working on this formula for quite a while now including requests to Smartsheet support. The formula is to raise a red flag when the end date is less than 3 days away with specific status options. I finally got it working the way I want with 1 exception. It does not automatically account…
Smartsheet IF(AND Issue. Will not work across two separate months
Hi, I've been using Smartsheet for around 6 months and would class myself as intermediate. However when it comes to Formula's I'm certainly a novice! I have a Smartsheet that acts as a Hotel rooming list. Users can load their Hotel Check In Date and Check Out date which then prompts a formula to count up the nights that a…
Calculating Rolling Average Using AVG(COLLECT())?
Hi, I am attempting to create a rolling average to replicate a "trend line" one might see in excel graph. Having trouble visualizing the formula I might use, but landed on AVG(COLLECT()) as a plausible option. Hoping to poll the group. I've attached a photo of the sheet. Essentially I want an average of all preceding…
How to handle a very long list of (multiple select) choices in a form
I am new to Smartsheet and using it to replace old InfoPath forms. One thing I cannot find an elegant solution for is expanding fields/sections. Basically, I want to send an instructor a form that asks which courses they would like to teach next semester. There are hundreds of courses. I will eventually want to use this to…
Extracting string from a cell
hi all, would love some help here for this-- I have a column that contain these strings in a cell: "relates to ABC-25277, relates to AF-4329" "is caused by ABC-33334, relates to ABC-33362, relates to AF-10303" I want to extract the "ABC-25277" from the cells and populate into a new column.

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

Marrying three formulas together
I have three different formulas, containing various criteria, which I want to want to marry together and for which all things must be true: 1) project start date is less than 1/1/24; 2) go-live date is greater than 12/31/23 and 3) the project stage is "In progress" or "Waiting to start": =COUNTIF({HR Projects Project Start…
Automatic Parent by Child Status
Hi everyone, I'm working on a formula in my status column so that the parent line will automatically update based on what the child/children are. Here's the current formula I have that's not quite working: =IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Achieved") =…
COUNTIFS FORMULA
Can anyone help where the error to this formula is please? Just trying to count if a date appears in one column and a color appears in another. Should be easy right? =COUNTIFS(({Range DATE CREATED}, DATE@row),{Range Colour}, "RED")

Formulas and Functions

Discussion List

Help Article Resources