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.

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.

NEW! Your survival guide to formulas is here. Meet the Formula Handbook.
Hi Community, Whether you’re summing child rows, calculating budget minus actual, or doing cross-sheet data lookups with multiple criteria, formulas are a must for tailoring solutions to your needs. The new Formula Handbook is here to help formula beginners and pros alike. This template contains: A glossary of all…
Smartsheet Formula Reference to another sheet
I am creating several different budget sheets. In these I want to reference one reference sheet. For example in my current sheet: I am looking to say If Column names: HI Rate Code = HR Rate Code from sheet name: Reference Guide then bring over the # listed in Sheet Name Reference Guide, Column Name: HI Rate Amount. Help :(
Networkdays Help/Issue?
Hi, I have a formula that for networkdays: Here is the Formula. =IFERROR(NETWORKDAYS(TODAY(), [Due No Later Than]@row, Holidays:Holidays) - 1, "NA") However, anytime the days goes to negative it instantly goes to negative 3 (-3). So the calculation shoots from 0 to -3 the when the day shifts even though it should be -1, So…
COUNTIFS and OR formula referencing multiple contact columns in another sheet
Hello, I am trying to come up with a formula that would count the number of active projects for each member of our 6 teams. I am referencing data hosted in another sheet and I have multiple criterias for the COUNTIFS formula : The project has to have an active statut The name of the employee needs to appear in at least one…
SUMIFS and CRITERIA where @cell date = date in associated row in another column
Hello friends, Basic need description: SUM the invoice amounts in Column A IF date in Column B = date in Column C Using SUMIFS function: I am trying to SUM $'s in Column: "💲 Total Rental $ Amount {H}" → if @cell date in Column: "📅 System Date" = date in Column: "📅 Today {H} 🅰" Column: "📅 Today {H} 🅰" is populated every…
Summarizing Sales to a Quarter
Hi, I have a question regarding a formular. I am summarizing all Sales in a Column to have all sales for a quarter. It's possible to choose the impact months in a multi select dropdown. So it's possible that the impact is e.G. for july, august and september. But with the formular I am using now, the amount is being counted…
I need to create a subset of the data on one sheet that can be actioned and then linked back.
Hi, I have one sheet that is a listing of all commissionable groups. The team processes the commissions from this sheet. Of those, there is a subset of agencies that we prepay 50%. We want to create a separate actionable sheet that pulls the data from the commissionable group sheet for only those we prepay. Once those…
Is there are "CHOOSE" function? Calculating quarter from dates
My manager and I have been trying to find a formula that would calculate the FY quarter based on the "Start Date". We haven't been able to use a lot of the formulas we found on the Community because our fiscal year starts in Feb. instead of Jan. We've found a couple of solutions that's working for us now, but it's really…
Filter to capture all rows within the current month
I have a table called Marketing Calendar. It has columns for Event Name and Event Date. I want to report to the marketing team both the Event Name and Event Date for the upcoming month. I also want one for the events within the current month. Purpose is to create a newsletter to tell our membership what classes and…
Formula for auto populate?
In the REP field, every rep is assigned to an area. Would there be a formula that if I choose SALES REP 1 it will auto-populate on the state field AREA 3? Thanks
I need to result the column's name when using a JOIN(COLLECT) or INDEX(MATCH) formula
Previously, Row 1 of my smartsheet contained the column's name. This way, I could write a JOIN(COLLECT) formula to return the column's name if certain conditions were met throughout the sheet for that row. That worked until we applied column formulas to some of the rows. Now Row 1 can't be the column's name because of the…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Formula Help
Hello, Last formula I need to figure out to finish this template. @Kelly Moore You have been so incredibly helpful, if you are able to help me on this last one I owe you one! I know I have an error in the formula or maybe using the wrong one. I am trying to pull the Start Date and End Date on the current sheet, taking into…
Need to calculate a date based on data in another sheet
I'm creating what is basically a notification system that's too complicated for just Automations. In a one sheet I've set up a Cadence by a Category. So the category might be Webinar. The cadence is captured in multiple columns labeled First Push, Second Push, etc. Each of those houses a number representing the number of…
Display Issue with Formula
I have another equation that is not displaying the value I need to pull down. I think I have the formula right, it's not coming back with an error, yet there is no numbers displaying. Ultimately I want it to pull down the generation from a previous year so wer can compare. Ideas/ Thoughts?

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags