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
IF ERROR Formula when trying exclue zeros from average
I am creating a sheet for PM weekly time tracking. The top parent row (blue) is YTD roll up of total hours, as well as a column for average weekly hours (<---- this is my issue but I'll to that shortly). I have a child row (green) for each quarter, and then grandchildren rows (white) for each work week. The child rows also…
Populate Start/End Date based on Status change
I use the Card View for quickly creating tasks and tracking their status/state. I'm trying to see if it's possible to take this further and populate the Start Date and End Date fields when the task first enters a specific state. See screenshot attached.
Net duration between 2 dates AND times
Hi folks, Ok, this is a tricky one: I'm trying to set something that was requested by many people on this community forum - a NETWORKDAYS that can account for TIME as well. In other words, the amount of working days/hours between to periods (that accounts for. There is this amazing…
Need help with date formula
I am trying to create a line chart widget. The chart would display the date an incident or incidents were reported on the X axis. The y axis would display the number of incidents scale. The widget would report the incidents per the last 90 days. Only the date an incident(s) was reported would display. in the chart.…
Adding a Title to a COUNTIF formula
I am looking for guidance as I feel like I am missing something really simple. I would like to be able to have a title(label) included in my COUNTIF statement. This is the total count formula I have for the parent row: ="May 2018" + " (" + COUNT(CHILDREN()) + ")" This formula displays in the cell as May 2018 (3) ~~~~~~ I…
Sum total number of cartons by date
I have vendors fill out a form every time they have a shipment leaving their building. I want to create a widget on the dashboard for our leaders that show the total number of cartons leaving our vendors each day. (Bar Graph) But first, how do I create a report or formula to sum cartons by date, without me providing the…
Formula to count rows in a date range across columns and status
I am getting suck trying to create a =COUNTIF formula from a sheet that has my date column, and several other qualifying status columns.Any advise would be greatly appreciated. Here is where I am at so far and I am getting an #INVALID Data Type error. =COUNTIFS({Sheet1 Range 3}, AND(@cell <= TODAY(), @cell > TODAY(-30),…
IF Formula for a sheet
I am trying to get this formula to work correctly. I believe I am close however it is not working correctly. I have attached a screen shot.
Nested IF using additional AND logical functions
I am struggling with creation of formula for defining tasks status (Green, Yellow, Red). Getting the UNPEARSEABLE or INCORRECT ARRANGMENT SET error messages. My formula is right now as follows: =IF(ISBLANK([RESPONSE DATE]88); "Gray", IF([RESPONSE DATE]88 <= [ORIGINAL DUE DATE]88; "Green", IF(AND([RESPONSE DATE]88 >…
Cross-sheet "countif" formula with three criteria
I am struggling with another Countif formula. I have a working formula in one column that counts proposals filtered by month and year. I am trying to modify it to add another criteria so I can count proposals filtered by the month year that also have the sales stage of "Asset Management Plan." The formula doesn't give me…
Help Article Resources
Trending in Formulas and Functions
Need a formula for % complete for all phases on the parent project summary row. Row is non editable
I need an average formula in the parent project summary (row 23). Formula should include all phases (initiating, planning, executing, closing). The parent row is un-editable and not allowing me to enter a formula.
Formula for return value from a seperate sheet column if another column is one of 3 values
Hello, I am trying to create a formula that returns the value of a lets call it Column A in a seperate sheet if Column B in that same seperate sheet is any of 3 different words. Here is the formula as I currently have it. =JOIN(COLLECT({Scopes Range 3}, {Scopes Range 2}, OR(@cell = "In Progress - On Time", @cell = "In…
If Formula help
Hello, I am trying to write an IF formula for my smartsheet that adds the letter A, B, C or D or E to a column pending on a dollar amount listed in another column, but I cannot seem to get the ranges to work right and everything is listed as either A or B. Current base formula: =IF([Total Assets]@row >= 1000000, "A",…
