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
Sign in and ask it here! The community's got your back.

📈 Wondering if there’s a problem?
Check out the Status Page and subscribe for notifications.

💡 Share your knowledge!
Help others by answering questions.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Formula with multiple IF(AND statements
I am having trouble with a formula that will return a 1, 3, 5, or 10 based on a combination of two columns. Anything selected as REPORTED will equal 1. Easy enough. But in the same column, If FIXED Other points will be rewarded based on the Category it is paired with. Here is what I have so far: =IF([Reported or Fixed?]1 =…
Complex Time Calculations - difference between created dates
Hi, Having an ongoing battle here with trying to work around the system generated "Created" date/time stamp. I want my guys to fill in a form when they start and stop doing work on a project in our production line, and then evaluate the time spent on each serial number job. My grid of sample responses looks like below: You…
Linking Check Boxes Across Two Sheets
I need to have one sheets check box update a different sheets checkbox. Is that possible? I want the Primary Edits check box to update the Design Amends check box
COUNTIFS nested with AND
Hello community, I need your help. I want to count how many rows meet the following conditions: 1. The rows where the value of the column {ESTADO} equals "Espera". 2. The rows where the value of the column {RESULTADO} is empty. 3. The rows where the value of the column {DATE} is greater than 7 days but less than 14. I am…
Is this even possible?
I have a process document with columns with start dates and due dates. The process steps do not change from year to year. What I would like to do is trigger some action or create a formula that once an item is checked complete, the start date and due dates advance by 365 days. It would be great if the complete box was then…
SUMIFS Multiselect Dropdown
Creating an estimating sheet, that selects multiple people to a task. Each "role" has a different billable "rate" Sheet #1: "DATA" 2 Columns: Roles and Billable Rates ~ 10 roles, changes per project Sheet #2: "Select & Summary" 2 Columns: Assignee: Multiselect drop down or each role. (would love a VLOOKUP for the…
Formula skipping rows
Hi all, I'm trying to use the below function to automatically change the color of Harvey balls to red, yellow, or green based on the length of time till a task's due date. =IF([Due Date]@row >= TODAY(+61), "Green", IF([Due Date]@row >= TODAY(=31), "Yellow", "Red")) However, for some reason some rows show "#invalid…
Math Formula Error
Hi, I am trying to create a formula to divide the Impressions column by 1,000 then multiply it by the Total Price column. This is the formula I am using "=VALUE(Impressions@row / 1000) * VALUE([Total Price]@row)". Not sure what's going wrong. Total price is a column formula that auto fills each cell with the $5.95 price…
Year Function
I have a date column [Updated on]. I a formula to count all the items in the column which occurred in 2021. I have the following so far. I am getting INVALID DATA TYPE. =COUNTIF([Updated on]:[Updated on], YEAR(@cell) = YEAR(TODAY()))
Formulas
I am working on counting if criteria is met, but want to skip if the cell is blank. I have two formulas that work separately, but when I combine them I get an error. CountIf =COUNTIF([Connected to Confirmed]@row, "<7") Skipping the blank cells =IFERROR([Connected to Confirmed]@row, "") I'm not sure what I'm doing…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

countifs and a date that falls between two dates
Please help! I have two formulas that I am struggling with. I am trying to count how many tasks have been completed/closed within the past fortnight. I came up with the following formula but it gives me the 'invalid operation' error. What am I doing wrong? Formula=COUNTIFS({LV_ BLD CONST QUERY REGISTER _status}, "closed",…
I am trying to compare 2 date columns with an odd result
I am trying to count the number of submissions on a daily basis with =COUNTIF({Loans Range 1}, =[Begin Date]@row). Most of the formulas return the correct number of submissions. What it looks like it is doing is applying the submissions entered after a certain time (5 PM?) to the next day. Dependencies are disabled…
Revise Formula to include a but
My current formula in one column that is working out is the below: =IF(OR(CONTAINS("Term", [Column1]@row), [Column2]@row = 1), "Green", "Yellow") For the next column I need a revised formula to say "if the term is in column 1 and column 2 is check BUT Column 3 is checked then it will give me a red ball" What do I need to…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags