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

I have the Year and Month on a sheet. How can I have the summary look at both to count?
I would like to create a summary if the month is Sept AND the year is 2024 to count. I have found that I can use the and but keep getting an error message This is the Formula I have in the Summary that is coming up #Unparseable =COUNTIFS([Next Renewal Year]:[Next Renewal Year], "2024") AND(([Next Renewal Month]], "Sept")
Formula Help
Hi All - I am hoping someone might be able to help me. I am terrible at formulas and I cant seem to figure out how to pull what I need on my own so I copied a formula from another sheet we have at our company. The issue is that it returns it multiple times. So the expected result is "four", the formula returns "four"…
Are You Able to Join Two Columns into a Data Shuttle Dynamic Dropdown?
Hi everyone, I'm hoping someone here can help me out with a challenge I'm facing in Smartsheet's Data Shuttle. I've been trying to use expressions to combine two columns into a single dynamic dropdown column in a target sheet, without changing the source sheet. For example, I want to combine Num and Name into a format like…

Formulas and Functions

Discussion List

Help Article Resources