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
SUMIF of CHILDREN on Different Sheet
I want to create a roll-up summary sheet from data on another sheet. The source sheet/data, let's call it "Pricing": The summary sheet, lets call it "Summary": What formula can I use to grab the Cost and Price from each Store # on the Pricing Sheet and automatically populate the Summary Sheet? I imagine it's some…
VLOOKUP resulting in some references coming back as #NO MATCH.
I have a master Smartsheet people fill-out a form for that submits purchase orders made. I have a corresponding Smartsheet that people fill-out a form for to indicate when a specific PO has been received. In the master Smartsheet I am attempting to use a VLOOKUP that looks at the PO number from the master Smartsheet and…
SUM IF CONTAINS?
Hi what's the easiest way to add IF contains? I have a sheet with many different rows with long string values and I'd like to get a total of how many times a particular value within the string appears. For example: Value 1: "ABCDED" Value 2: "ABCDDD" I'd like a count of how many times D appears in that range of values. (it…
sum the number values in a drop down box
Hi all Hoping for some help with this one please column name is Units and it is a single select drop down box with the choices of selecting a number between 1 and 10 i would like to know if i can create a formula to sum the total i.e if one cell has 1 in it and the 1 below has 2 it sums it to 3 - seems easy if its just a…
COUNTIFS Formula Issue
Hi all, I'm trying to count how many times a particular field appears in a range across two different sheets, but I keep getting an #UNPARSEABLE and I can't figure out why. My formula is below: =COUNTIFS({2021 Completed Sim Equipment}, "24 Week Airway Trainer",[{In Progress Sim Equipment}, "24 Week Airway Trainer"]) If I…
SUMIFS
Hi, I'm looking to sum the cells in a column if another corresponding column is either of two values. I'm getting #UNPARSEABLE though! 😕 =SUMIFS({Data Range Spend}, {Data Range Award Type}, OR(@cell = "Direct Award”, @cell = "Mini-Competition”)) Any thoughts welcome! Thanks, Derek
Comparing dates in 2 different sheets columns
Hello, I am trying to compare dates (like < or >) between 2 different sheets columns. Can someone guide me using IF statement to do this?
Summing FTE Hours available in the future
I am currently utilizing the current formula =SUMIF({Resource name}, [Resource Name]@row, {Weekly Scheduled Hours}) to determine how many hours a resource has available. Since I began using this, some assignments have ended, but resource utilization is still being summed, resulting in weekly hours scheduled being >40. How…
Using IF with greater than or equal to a number
I want the "Complete" column to show "yes" if the total reviews is greater than or equal to 3. I cannot figure out what I did wrong with the formula above. Thanks!
How to set a formula to repeat when adding new entries at the top of the page?
I have a sheet that captures employee's leave requests to show up at the top (most recent first)of the "grid" and on the "calendar" and I need it to occur with every entry. How do I set it to ensure that every entry will be displayed and automatically filled with the formula fx=[employee name]1. The formula pulls the…
Help Article Resources
Trending in Formulas and Functions
COUNTIFS formula for any future date?
Hi all, I am trying to create a COUNTIFS formula that counts if the due date column on another sheet is today or any future date. I tried =COUNTIFS({due date column's range}, >=TODAY()), but it is not working. Any advice would be appreciated! Thank you.
Sheet Summary Field - Count Value from Dropdown
What's wrong with this Formula? Getting an Invalid Ref
I am trying to get a unit count between a date range.
I am trying to get a unit count between a date range. I have two difference columns from one sheet and two difference columns on my main sheet I am referencing. I keep getting unparseable. Is there a way to to get the number I want with using the countifs formula? Or should I be using a different formula? This is the…