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
Find This Month
I'm trying to add a formula that will tell me if a date in a cell is this month. If a date is present in the reference cell, I get an INVALID OPERATION error. What am I missing? =IF([Date Completed]@row = MONTH(TODAY()), 1) Thanks!
Adding a condition to recognize if the external reference cell has any data or no data at all
I have a metrics sheet that pulls data from another sheet. I want the formula to mirror the way the Filter feature works to say "if this cell has any data whatsoever in it (in this case any name) then count it. if the cell has no data, then don't count it." This is how I have started my formula: =COUNTIFS({Post Closing…
My SUM formula yields a maximum of 56, but I want to cap it at 40.
I have a form where various answers to questions will give the applicant 2 points. If the applicant answers all questions, they would get 56, however, we are capping it at 40. How can I stop the counting after 40? Thank you!
Choose Older Date between 2 dates and then calculate # of Years
Hi everyone! I have a list that shows hire date and in some instances a rehire date. I want to be able to calculate the years of service based on which ever date is the newest. See snip. This is the formula and it calcs correctly on the hire date, but returns blank if there is a rehire date. What am I missing?? =IF([Hire…
How to create a SUMIF with ISNUMBER
I am trying to add three separate columns together, but sometimes, one column will have text in it instead of a number. Is there a way to get the formula to exclude anything that's not a number? My brain isn't functioning high enough to come up with something that works lol. This is my current formula: =IFERROR(Rate@row +…
Can I populate a multi select contact list off of what is selected in a multi select drop down?
I would like to populate a multi select contact list in the Master Sheet below based on what is selected in a multi select drop down (Project Requirements). The Lookup Sheet on the right lists what should be populated based on what is selected. It's only reading ONE project requirement. Any assistance would be greatly…
At Risk Flag when Past Due and is not one of multiple status options (drop down)
I am trying to come up with function that raises flag if past the due date and status is not: 'CANCELLED', 'Customer Testing', 'Pending Sign Off', or ' SIGNED OFF'. I have tried: =IF(OR([Current Status]@row <> "CANCELLED", [Current Status]@row <> "Customer Testing, [Current Status]@row <> “Pending Sign Off”, [Current…
Identifying Duplicates
I am working on a sheet that contains IP addresses. When reports are added to this sheet, I am currently going through the data and marking any duplicates. I want to find a way to either use conditional formatting to highlight the duplicates or any method that is available. Is this a possibility without inputting the value…
Formula Needed
Want to select a state (Example: CA = $60 per day) Start date column (1/24/22), end date column (1/27/22) Sum total in next column making total $180. How can I make a formula? Hopefully I have explained it well. 3 days x $60=$180
Help with Join(Collect to identify dependencies
This discussion was created from comments split from: JOIN(COLLECT 2 CONTAINS criteria with multiple returns Cross Sheet.
Help Article Resources
Trending in Formulas and Functions