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 with 2 criteria
Can't get the right formula for the task I want to perform - as follows - IF the PRODUCT column = "MMS", and the POSITION column = "CURRENT - CORE", then SUM the JAN'18 FCAST column basEd on the following range - JAN'18 facst19:JAN'18 fcast160 Latest formula version I tried is: =SUMIFS(JAN'18 fcast19:JAN'18 fcast160,…
A Sumif newbie needing help
Trying to write my first sumif equation and having a little trouble. Column Headers: Fabricator and Line Total If Fabricator: American* then sum Line total Here's my "formula" =SUMIF(Fabricator2:Fabricator40, =American*, [Line Total2:Line Total40]) Any help with be appreciated
Formula Help - 2 columns and % Calculation
I am trying to give a percentage based on how 2 columns read. Column PassFailA and Column PassFailB. If both show Pass 100%, If A is Pass B is Fail or Vise Versa 50% and if both Fail 25%
Estimate End Date, Actual End Date, Adjusted End Date flagging
For my projects I track Estimated/Actual Start and Estimate/Adjusted/Actual End dates. I need to have a status flag indicate yellow if the Adjusted date is slipping past the estimated end date, and then when the end date is adjusted and justified, to update the status flag back to green. Is anyone tracking like I am? Do…
Type selected - allows certain columns to be updated
The checklist type smart sheet will be setup with columns called Vendor, Type, Doc 1, Doc 2, Doc3. What I want to do, is depending on the type selected for a particular vendor, the doc columns will grey out for those doc types needed for that specific doc type. For Example: Vendor 1 Type Critical All doc type columns would…
Nesting a SUMIF function
Hi community! I have several columns of numbers that I want to add up in different ways. Next to these are two checkbox columns. I want to add up each column individually if both checkboxes are checked. So in the example I have a series of data columns Q1 to Q4, each with a row label and next to each row label are 3…
Formulas
I want to create a formula in a cell that subtracts a certain number of days from another cell that is linked to another persons Smartsheet. How would I do that? My efforts have failed.
Traffic lights count by colour if children
Hi there, I'm trying to count the number of projects according to their traffic light status across about 7 sheets. Given I want to display this in Sights as a Metric, I can't use a report so I have a sheet linking to the summary information from each sheet. When I use the Countif formula to count those that are "Red" it…
Automatic Checkbox
Hello I am trying to get a checkbox to automatical put a checkmark in it when a certain status (done) is written but I cannot get it in the conditional formating. Also trying to get the rule the other way as well so that if checkbox is checked that status changes automatically to done but can't seem to find it in the…
Finding exact value with many criterions using joined index, match and sumifs functions
Hi, everyday I'm working with lot data, there is many of columns and even more of rows. And there is no specific function for finding text when several criteria should be used. For finding exact value we can use index and match function. For finding number we can use sumifs function with many of criteria. I had problem…
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…
