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
SUMIFS criteria headache
Hello all, Typically, building a formula through Smartsheet doesn't give me any issues but the SUMIFS formula specifically within Smartsheet is making me pull my hair out. I have 3 columns that I need to use in the formula. -Job Code -Needed Position Headcount -Building Status I need to add the total headcount of each job…
Formula -weighted for specific criteria
I have a complicated request. I want to create a formula that will provide a % when a row is designated as a "critical path" item. See screenshot. As a box is checked in the column for each cabin name, I want to get the % to update for those critical checked boxes. Is there a way to do this? I already have a formula that…
Vlookup + sum in a column
I am creating a template for keeping track of employee's leave. Column "Accumulated Leave" has the vlookup function to return the "leave days" taken by employees. the formula I have is as below: =VLOOKUP([Employee Name]3, $[Employee Name]8:$Duration50, 5, true) However, when I show the same employee on an another row, it…
Cross Sheet Reference Unparseable
I am trying to use a COUNTIF statement to reference another sheet and count the number of "V" s so I can create a metrics sheet for my DashBoard Creation. I have attached a snip of it below but all i get is #UNPARSEABLE when i try it. I must be missing something, but I am unsure as to what. Received the #INCORRECT ARGUMENT…
automatically move date from one column to the next
I wondering if there was a way to move data from on column to the next when information is inputed into another column. For example if I create a column in front of wk 1 allocation% and input a %. I would like week 1% to move to Week 2, week2 to move to week 3 and week 3 to move to week 4. Week 4 can just go away or I can…
Counting Dates within a range
I am trying to get a count of the number of rows with dates that meet one or another condition. I know I cant use Countif so I planned on adding a column next to my date column to capture the data. One condition is that the date is either within the next 30 days OR the date is in the past. Here is what I have so far and it…
Is it possible to count a Blank Cell only text is filled out in another cell?
Currently I'm using this formula =COUNTIF(Status:Status, "") to summarize all the blank cells in the Status column. The issue is that it also counts the Status cells that are just blank rows at the bottom of the sheet. Is there a way to count only the blank Status cells if there is also information populated in the…
Invalid date causing error in Formula
I have formulas written based on a date but some of the dates in my sheet contains blanks or text. So in my formula, it is giving me an Invalid Data Type error. Is there a way to determine if the date is valid date in the formula? I can check for blank, but dont know how to check for anything else. Can you use ISDATE in…
Formula Query
I'm at a loss for how to perform a desired calculation within a sheet. I have a list of rows that are assigned to owners (contacts). Each row has a % complete. I want a formula that will tell me what a given contacts overall completion rate is. * Look up name in Owner column * Add up all percentages in the "% Complete"…
Formula not refreshing when date changes - Can't tell I'm connected?
Hello ~ I'm new to Smartsheet and would appreciate some assistance. I have a formula that changes the color priority as the next action due approaches or passes, reflecting if the task is pending action from others: Rows that are marked "Done" then gets conditionally formatted to Grey (this was a workaround to not having 5…
Help Article Resources
Trending in Formulas and Functions
Index Match on same sheet only matches to the first key found at top of row
The Index Match on same sheet only matches to the first key found at top of row where I am expecting it to look at all rows in the sheet. Here's the formula in Col3. =INDEX([Capacity 1]@row, MATCH([Collapsing 1]@row, Key:Key, 0)) And the example is below: You can see that the ABC12345 under Collapsing 1 col matches to the…
Can an automation reference a column from a cell value?
I'm trying to track which users are granted access to various systems. What I would like to do is to have one grid which lists all the System IDs (and various properties as columns) with one system per row, and a unique System ID for each system. Then I would like a grid that lists each user as a row, and the column…
Formula to populate a cell based on another cell's formula criteria
Hi Again, I have a formula in row 1 of column Project Status that identifies the last value in the column that has any status except Not Started: =INDEX(COLLECT([Project Status]:[Project Status], [Project Status]:[Project Status], <>"Not Started"), COUNTIF([Project Status]:[Project Status], <>"Not Started")) In the example…
