Formulas and Functions

Archives for September 2018

COUNTIF
Hello and good morning Smartsheet community, I know you can use the =COUNTIF in order to count the number of times a (SINGLE) name or word appears in a range of cells. My issue is that I am trying to count how many times the word safety is used in a range of cells where the word safety is used in a sentence and not the…
Using Multiple IF Statements
Hello, As Smartsheets does not allow for multi-select dropdown lists, I am trying to work a way around this using checkboxes and IF statements but keep running into a roadblock. I want it to say: If the "Chemotherapy" checkbox is selected in Column A, Column E says "Chemotherapy" . If the "ICU" column is selected in Column…
Count children & grandchildren of multiple rows under main parent
I'm trying to count the grandchildren status of multiple parent rows, and show a summary under mainparent (binos/mgmt/dmi). I'm using fx="Complete (" + COUNTIF(CHILDREN(), "Complete") + "),In Progress (" + COUNTIF(CHILDREN(), "In Progress") + ") , Not Started(" + COUNTIF(CHILDREN(), "Not Started") + ")" to get the summary…
Formula to count # of dates per month in a date column
I am trying to create a formula that will tally, by month, the number of date entries in a column. (See screenshot attached). Our non-profit provides ongoing financial counseling to veterans and each person is counseled more than once throughout the year...so I want to be able to tally each month how many new veterans we…
Add up all PAST DUE Items
I would like to add up all the action items on my Action Item log that are Past Due so I can show this on my project dashboard. How do I construct the COUNTIF statement to achieve this? Thank you from the newbie!
Formula Help
Hey, nested IF statements are my downfall so I am hoping someone can help me. I have a column entitled "At Risk" and it's formula is this - =IF(AND([End Date]5 < TODAY(), NOT([% Comp]5 = "1")), 1, 0) It puts a flag in if the % Comp column is not marked 100% and the end date is less than today. However, there are some items…
Days in progress formula with multiple criteria
Hello, is it possible to run a formula that gets me the following? If the status is “Not started” or “in progress” then count the number of business days between “date presented” and today, and if the status is “complete” then Count the number of business days between “date presented” and “date completed”
Get Max value with criteria check
I have Table with values as below and now I would like to get max value. How Could i can achieve the Results Required ?. Col1 Col2 A 2580 A 2380 B 1860 B 420 C 860 D 2760 D 2710 C 2750 Max Value -- A = 2580 B = 1860 C = 2750 D = 2760

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

Using a checkbox in formula
Hello All, My existing formula (working correctly) inserts text of REORDER NEEDED or OK option , formula below: =IF([Stock Quantity]32 < [Reorder Level]32, "REORDER NEEDED", "OK") I would like to add a third text insert of "ORDERED" when criteria of a checkbox has been checked in the ON ORDER column -or a date ordered…
Update Assigned Person only for active phases
Hi, I’m using Smartsheet with a Master Sheet(All over Projects Basic Summary) and Individual project sheets that have multiple phases. When I change the Assigned Person in the Master Sheet, I want: •It to update only phases that are Not Started or In Progress •It should not change phases that are Completed Is this…
% Complete column automation populating '100 (text) instead of 100% after recent update
Hi everyone, I'm hoping someone has run into this and found a fix. After a recent Smartsheet update, my automation that sets the % Complete column to 100% when a task is marked complete is now populating '100 instead of 100% — storing it as text rather than a number. This is breaking my downstream schedule logic that…

Formulas and Functions

Discussion List

Help Article Resources