Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template to view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.

Product questions?
Ask it here! The community's got your back.

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Count Ifs Formulas
I need help figuring out why this formula is returning Invalid Data Type: =COUNTIFS([Conversion Status]:[Conversion Status], "Won", [Date Requested]:[Date Requested], MONTH(@cell) <= "3", [Year]:[Year], "2017") I want to count the number of lines that have a Conversion Status of "Won" that also took place in March of 2017
Form get value as tekst from som users
I got a form and the value is present as text some on some users and as number on others. Why is that ? The calculations get wrong withe the texts. Regards Jonathan
Counting Occurence of String of Text in a Column
Hi, I need suggestions on how to count string of text on a column. For example, i need to count how may GRC are there on the sample below: GRC Aer, GRC Aer, CDIR, GRC HTM etc. etc. Thank you!
SUMIFS criteria
Hi All, I'm trying to do a SUMIFS, and whilst I can return a result individually using SUMIF, when I try and combine I get #INVALID OPERATION. The formula I am trying to use is =SUMIFS({SERVICE DETAIL}, LEFT(@cell, 3) = "Col", {FEB 18}, {SERVICE TYPE}, "Transition", {FEB 18}) RESULT = #INVALID OPERATION If I separate it…
Countif with Checkboxes
I need help doing a countif with checkboxes. I have a formula I need to countif in column Project if it has a value of Yes and column Parent Task is check. I also need it to countif column Project also has a value of partial. This formula worked until I tried to add the Parent Task column equaling 1. Anybody know what I am…
If(and(countif equation question
Attempting to make a status bubble roll up where the parent will turn green if there are any blank ("") cells except for when all child cells are blank. Below are some of my failed attempts: IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "") <> COUNT(CHILDREN())), "Green") results in blank cell only when all…
What would the formula be for.....
I need to sum or count the number of people in my sheet that are under 17 and over 65, based on who is checked. Can I do this using a cross sheet formula so all my totals are together? I have both DOB and age columns - can the formula be on the age column or do I need a value column to set age parameters?
Comparing 2 date fields and setting symbol
Hi. Just getting to grips with Smartsheet. I have pairs of date fields: Forecast Date and Actual Date. I would like to set a red status symbol if the Actual Date is past/older than the Forecast date. How can I do this? Alternatively I can set a check box if setting a symbol is not possible.
Formula
I am trying to write a formula that has multiple steps to it. I want it to see if the project is completed then to give me the Max date of completion.
IF Statement Help
I have a sales price database with one column for a retail price and another for a sale price. I'd like to make a 3rd column with a formula that populates the Save __% for our Marketing team. I made this formula which works with one small issue I'd like to resolve if possible. =IFERROR(("Save " + (ROUND(1 - ([Sale…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Using JOIN/COLLECT and an OR?
I'm trying to write a formula using the JOIN and COLLECT options. However, I've hit a sticky point when I want to include an OR criterion on the COLLECT portion. My current formula (which works perfectly) is: =JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, "Live"), ", ") However, I want to…
INDEX/COLLECT with both MATCH and CONTAINS criteria
I am looking for help with a cross-sheet INDEX/COLLECT formula. I need to MATCH in one criteria and CONTAINS in another criteria. I can get them working separately but I don't know how to combine into a working formula. Source Sheet: Destination Sheet: In the Attending School column in Destination Sheet, I'd like to…
Parent Color Symbol, Based on "Average" of Child Symbols
Okay, here I am again! On the Project I am working on, the Stakeholder wants parent rows for no reason other than to be able to expand and collapse the data. I nicely suggested that since the data would only be for 1 year (broken down in quarters and about 5 users listed each quarter), that since the data was not excessive…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags