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

Upcoming Events

Brandfolder Collaborator User Guide
Get ready to seamlessly upload and organize content within your Brandfolder! This on-demand session is designed to help you become a confident contributor and collaborator, ensuring your team has the right assets at the right time. What You’ll Learn: Maximize Features in the Asset Modal: Learn to navigate the asset modal,…
Brandfolder Admin User Training
Managing digital assets doesn’t have to be complicated. Join us for this two-part, on-demand webinar series designed to give Brandfolder administrators the confidence and skills to create smooth, efficient workflows. Whether you’re just getting started or ready to optimize your setup, these sessions will help you unlock…
Smartsheet Customer Success Onboarding (Japan)
Smartsheet カスタマーサクセスオンボーディングは、Smartsheet についての基本を学びたい方向けに毎週開催しているインストラクター主導のトレーニングコースです。ウェビナーは、始めるための基本、共同作業、およびさまざまな方法での作業の視覚化の基本など、Smartsheet のご利用を開始するために必要なすべてを網羅しています。

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Health Indicator Claculation
I Am using the below formular in my Health Indicator but even though my project is on track My overall project indicator is showing Red. What have I done wrong any assistance please :-) =IF(AND([End Date]@row < TODAY(), [% Complete]@row < 1), "Red", IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY(1), (TODAY(1)…
Sheet Name links not taking user to correct sheet row
Hi there, since yesterday I have noticed that the Sheet Name column links in reports are not taking the user the correct row in the source sheet, as they have always done. There does not seem to be a pattern, sometimes it takes the report user to the top of the source sheet, or to a random row, or the odd time to the…
Calculate days remaining until task due date
I'm trying to calculate how many days remain until a task's Due Date, but only if the Status is not "Complete." If it's complete, I want the cell to stay blank. What formula should I use?

Formulas and Functions

Discussion List

Help Article Resources