Formulas and Functions

Archives for March 2021

Trouble using countifs referencing another sheet and adding either "NOT" or an "OR"
I am trying to write something that will do multiple countifs and either include a range of ORs or allow me to exclude rows that have a particular response in a given column. =COUNTIFS({NEOP Tracking Range 2}, [Column5]12, {NEOP Tracking Range 4}, [Column5]@row) This works great. Now I need to only see employees that have…
IF/AND cross-sheet formulas
Hi there, I'm getting error messages with my formulas and was hoping someone could help. I'm trying to create a formula with the logic below: IF [Lot Number]@row in my target sheet = Lot Number in the my inventory form AND [Status] in inventory is "Approved" THEN check a box in my target sheet. Whenever I try to reference…
Tracking PTO
Does anyone know of a formula that I can use to keep track of PTO days? Screenshot shows my layout. I want the employees to be able to enter their days off throughout the year and the total be subtracted as they enter. I would also like the total days to carry into the next month. Any ideas??
Formula for weighting tasks on a scale of 1-5
I have a sheet that is a master list of all of my ongoing and upcoming projects. I have a multi-select column that has a list of tags for product type. I would like to have a column that looks at the product types and assigns a number to indicate complexity. For example: Product Type 1 = 1 (simple) Product Type 2 = 2…
Exclude weekends from date calculation
Hello, Is there a way to exclude weekends from a date calculation? I have an application with start and end dates and I want to calculate the number of days between the start and end date, not counting weekends. I have this formula working but it includes weekends so if someone takes a Friday and a Monday the calculation…
Index based formula:
I have a separate sheet with a 2-column Table Index. (Eg: "Reference Sheet Column A" has all 50 States. "Reference Sheet Column B" has the corresponding sales tax for each State.) On another sheet, I have "Column A" that allows selection of a State from a dropdown list. In "Column B" I have the "base price" input. I need…
Index Match formula pointing to Multi Select Range
I am using an Index/Match formula to pull contact info from a Team List smartsheet into a Schedule smartsheet. For the most part it works just fine. In the Team List smartsheet I have a column named "Trade" set up as a Multi Select dropdown. In the Schedule smartsheet I have a column also named trade and it is set up as a…
At Risk Formula
Hi Smartsheet team, I am having some issues coming up with an "At Risk" formula and would appreciate any help I can get. I am trying to make the AT RISK flag appear if the "status" of my task is "Not Started" or "Delayed" within 30 days of the "End Date". Any help you can give would be greatly appreciated!
Do shared users need access to other sheets from which cells are linked?
We have a sheet which pulls most of its data via cell link. We do not want to provide access to the reference sheet to the shared users. Will they still be able to see the data in the sheet we do share with them, or is it like a report where they would need access to the reference sheet? Thank you!
How to indicate a row is a Parent row Yes/No
Hello, I need to create a column where it indicates if the row is a Parent row. I tried with =Parent() but it does not return anything. I need this column to create a lot of other formula columns where different conditions apply. In Microsoft Project, they have a default column call "Summary", which does the same thing. It…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Upcoming Events

Brandfolder Collaborator User Guide
Get ready to seamlessly upload and organize content within your Brandfolder! This on-demand session is designed to help you become a confident contributor and collaborator, ensuring your team has the right assets at the right time. What You’ll Learn: Maximize Features in the Asset Modal: Learn to navigate the asset modal,…
Brandfolder Admin User Training
Managing digital assets doesn’t have to be complicated. Join us for this two-part, on-demand webinar series designed to give Brandfolder administrators the confidence and skills to create smooth, efficient workflows. Whether you’re just getting started or ready to optimize your setup, these sessions will help you unlock…
Smartsheet Customer Success Onboarding (Japan)
Smartsheet カスタマーサクセスオンボーディングは、Smartsheet についての基本を学びたい方向けに毎週開催しているインストラクター主導のトレーニングコースです。ウェビナーは、始めるための基本、共同作業、およびさまざまな方法での作業の視覚化の基本など、Smartsheet のご利用を開始するために必要なすべてを網羅しています。

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Construction Phase Count Formula
I'm trying to count how many projects are in the construction phase each month using this formula: =COUNTIFS({Master Project List Range 1}, "2 - Construction", {Master Project List Range 2}, <=Month@row, {Master Project List Range 3}, >=Month@row) It works, but I want to make sure it's also counting projects that are…
Formula shows UNPARSABLE
I am trying to create a formula, and I keep ending up with unparsable. The name of the field I am accessing is called "Deliverable Number". Now For example, this field contains 2 characters I want to REMOVE Original: DEL #10 - TO Portal Strategy/Solution Desired: DEL 10 TO Portal Strategy/Solution As you can see the "#"…
Logic for If this AND if that then....
Does Smartsheet have the logic capability for me to ask it "If Column location is East, AND Column Credit request is travel credit" then use the standard default amount of $500 in travel credit amount column? Basically, I want to add logic where the answer displayed is based on 2 columns and not just one.

Formulas and Functions

Discussion List

Help Article Resources