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
Sheet Summary/Sheet Summary Reports Questions
Hi, Is it possible that if you have several dates listed in the sheet summary on a project, that those can be pulled onto the sheet summary report not in a row, but all in one column? Then you can sort by the date and it will actually make them go in order? Instead of a row of dates, it would be a sortable column of dates…
How to Cross Reference Data from Another Sheet
This discussion was created from comments split from: How to Cross Reference Data from Another Sheet.
Extract a number which is sandwiched between text
This discussion was created from comments split from: extract part of a cell's text.
Issues with IF formula
Issue we need to resolve: The scope of work gets filled out by the client. The client has old information regarding location. In this example that location is 1-X-Kt/4.5. This location has been updated to 1-X-Kt/3.5 because of a remodel. We want the location to change to the updated location in a new column. Desired…
Time stamping checkbox
I am trying to create an IF formula on a cell to give me todays date when a checkbox is checked off. I tried the following but its giving me an error. =IF(DONE@row= 1, TODAY())
Date formula query
What formula to use, to calculate records from today till 6 months down the line? And this rule should be valid at all times. In other words, how to calculate sum of values eg: count of total number of projects starting from today (26/April/24) till 12 months down the line i.e. 26/October/24? For sum of values starting…
Index Match Help
Good afternoon, I'm trying to get my Index and Match formula to work, it was working correctly and has now broken and I'm not sure why. I Have a "Weekly Metrics" Sheet I'm Trying to pull information over to the "Weekly Metrics 2" Sheet I'm trying to get the data from Weekly Metrics to Weekly Metrics 2 by matching the dates…
IF (And statement with date columns?
I have tried several ways including Not(isblank and now using the current as blank fields and ISBLANK, and 0s, now just with quotes... it won't read both coluumns. it is say complete, even if only one column is not blank. i have another column with a similar formula that works GREAT, but it doesn't involve date columns.…
I am attempting to create a formula that counts the number of applications approved each week?
I have five different sheets collecting information about 5 different applications and each sheet has a date-approved column. I want to collect the number of approved applications for each application type, each week (1-52). I wanted to create a column formula that will count the applications that are approved if the…
Averageif within a date range
I am trying to get the average cost of contracts written within each month of the year. =AVERAGEIF({R2 Scheduled Date}, AND(@cell >= Feb2, @cell <= Feb3), {Total Gross}) is returning "$0.00" Any suggestions?
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",…