Formulas and Functions

Archives for March 2018

Referencing sheets
I am trying to decide between one sheet vs multiple sheets. I currently have one sheet but it has over 50-60 columns and there are sets of data that will be used by different areas. I know I can filter what is needed (to a point) with reports but it still doesn't give me the end result I need. Here is the breakdown in a…
Status Balls formula help
Hi, I am very new to this an am getting a bit frustrated. I have the following columns, Ship Date, Fabric Order Date, Fabric ETA, and Fabric Received or In Stock. The first 3 columns are date columns and the 3rd is a checkbox. I would like my status balls to due the following: If Fabric Order Date is blank, I would like a…
IF WITH 2 CRITERIA
I'm looking to make a formula that will do the following: search all instances in Column2 for "A" IF subsequent Column3 has a value of "YES"....if all "A"s = "YES" than return a value of "YES" otherwise "NO" I included an image that hopefully clarifies this a little more. in the image I show 3 "A"s in Column2, 2=YES and…
Multiple reference Formula
Greetings: Trying to create a formula that tracks task duration displayed by "symbol" in conjunction with another condition of the task:... complete, or not started. I was able to get the symbols to show up properly based on calculated duration. However, the bottleneck is related to the formula not picking up a symbol…
Count of cells in a date range
Hello, - I am fairly new to Smarsheets and not very knowledgable past basic formulas. I am trying to create a formula that will display the count of cells whose date is from Monday through Sunday, each week. Like how many open items were closed this week. I can't seem to figure out the criteria wording....…
RYG Balls-can't figure out
I've looked and cannot solve a problem I had solved before... I have a checkbox to denote when a task is completed. I have a due date. My problem is that I want my RYG to be green if the box is checked (that seemed easy to do) and Yellow if the due date is within 7 days AND the the box is not checked and Red if the due…
Using countifs across sheets to hold metrics for reports
We have a sheet that lists all of our projects (there are over 300 at the moment). We used to keep all of our metrics at the top of the sheet, but every now and again, someone would sort by a value that caused the metrics to get mixed into the project list. So when cross-sheet formulas (vlookup, countifs, etc) became…
Formula using Sum and Countif not returning expected outcomes
Here's a version of the formula that works. However I need to include a second value and get an error when I try. I'm sure it's the formula syntax. Not sure if there is a better way to get the right outcome. I basically am trying to determine % Complete OR Not Applicable Working with just Pass as a value…
Formulas in Columns with Dependencies?
Is there any way to work around dependencies in columns when entering in formulas?
Formulas per column
Hello, Smartsheet team: If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows! Is there a shortcut that will apply a formula to the entire column, or to a selected part…

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

index match
Hi All, I need a formula to identify keywords in a paragraph and assign the paragraph to a specific person. The goal is to determine a keyword in a given paragraph and then associate it with a designated individual this formula works in excel. =IF(ISNUMBER(SEARCH("scaffolding", A1)), "Summit",…
COUNTIF, CHECKBOX with CONTAINS BRANCH "MIL"
Hello, Please help and thanks in advance. I am trying to Count all the Key-User cells with a checkbox where the branch is MIL. I have tried the below formula but keep getting #UNPARSEABLE. Formula I tried is =COUNTIF([Key-User]:[Key-User], =1), CONTAINS ("MIL",@cell,[Branch;Branch]) Also tried…
Countifs drop-down multi columns
Hi, I'm unable to get countifs results on this sheet where GEO is drop-down list column allowing for multi values and STATUS is a drop-down list allowing single values. I'm looking to count the number of values GEO if the STATUS is COMPLETE. In this case: EU, COMPLETE (2) US, COMPLETE (1) AFR, COMPLETE (1) Any idea how to…

Formulas and Functions

Discussion List

Help Article Resources