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.
Discussion List
SUM formula is not working?
I am using form to add row (data) to smartsheet, But newly added row on the top of smartsheet is not included in autosum at the bottom of smartsheet. How I did it was went to the cell at the bottom I wanted to see SUM and highlited cells from bottom to the top. Rafal
Linking pages
Hey Guys, i am looking for a way to have vital data show up from one sheet onto another. I am using 6 sheets to keep track of project data. Each individual line in the sheets has all of the data for one project. In this line is a check box that states "Ops Scheduled". I need a way to have projects lines that do not have…
CountIf's with a date
Hello- I am trying to get a formula to calculate if the task is still open and the date is before today's date then the task is overdue. This is what I have for the cell and it keeps returning unparseable: =COUNTIFs({NITD Action Log Range 2}, "Open", [{NITD Action Log Range 3}, <=TODAY() ] Range 2 is a column stating if…
IF FIND RIGHT String
Hello, I’ve been searching all over the Smartsheet community since last week and was hoping someone could offer some assistance. I have a long Status value that I need to auto populate a shorter value in another column. I really just need a contains formula but I haven't seen that as an option here. I could do this for the…
If function
Please can someone tell me the formula I need if a box is ticked how to move an amount to a different column. so If mikes ticks is ticked how can I move the amount to amount outstanding. thank you Sue
Risk Status on my Smartsheet using formulas
I am using Blue, Green, Yellow and Red for my project status - I have a formula around it that will change the color accordingly. What I want to do is if the color ever turns Yellow or Red log it as a risk in another column. I can get it to work but if it is ever a risk I want it to remain a risk even if the task turns…
Cell Time Stamp
Does anyone know if there is a way to get a Date to generate when a project is set to approved? I want to be able to set a target date using the time stamp from when a concept drawing is approved to when we will need to ship samples.
If no Array formula and no Sumproduct, what can I use?
I have been trying to create a product cost calculator where every column is a finished product and every row is a raw material. I am really bad with formulas but I had success with both Arrayformula and Sumproduct in Google Spreadsheets. But I need my data in Smartsheet and I can not calculate the cost of one product. Do…
RYG Balls connected to Whether Items are completed or not.
I am looking to connected RYG balls to whether certain items are complete or not? I am not sure of the best way to do this- if a checkbox would be the way to connect color change based on status of task, or if inputting a "completion date" would do the same thing? I tried a couple of different formulas from these forums…
How to use data from another sheet as selection criteria?
Hello, I am trying to build a Report or ANOTHER sheet where I pull data only for Rows from another sheet that have OrderNr=OrderNr from the other sheet. The ordernumbers change daily. I need to be able to refresh automatically on a daily basis. Any recommendations how I should approach this?
Help Article Resources
Trending in Formulas and Functions
Formula and Function : COUNTIFS : Invalid Data Type (Cross Sheet Referencing)
I am working on some key metrics for one of our teams that track referrals. I am getting an Invalid Data Type and I know it is due to "blank cells" in two of the columns I am looking at. ISBLANK is not my strong area and unfortunately I cannot generate the formula using the AI Tools because it is not capable of cross sheet…
Count and Countif Formula adding 4th Statement
I have an existing formula: =IF(COUNT(CHILDREN(Status@row)) = 0, " ", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), " ") =…
How can I show the assignee of a parent task based on the current active sub task assignee?
I would like a parent task to show the assignee based on the subtask that is active at a current time. Is this possible? Ideally the task being active would be defined if the todays date is between start/finish dates. For example: Task 1 - Assignee = X Task 1.1 - Assignee = X - In Progress Task 1.2 - Assignee = Y - Not…