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
If cell in column contains "ASAP", change value of cell or match another cell's value
Hi Smartsheet Community! Issue we are trying to resolve: We have an intake request form that provides the client with an opportunity to request a preferred start and end date range or fill in with "ASAP". However, when the client enters in "ASAP" it causes the Requested Work Week column to have an error, which causes the…
How to calculate time off within business hours excluding weekends
I have a leave sheet I am trying to create that calculates the hours a person is taking off that are only within business hours and excludes weekends and Holidays. I have the below formula that works to calculate hours elapsed between two times and dates, but I cannot figure out how to get it to only calculate week days…
AMI Estimator Formula
Hello! Has anyone set up a formula in Smartsheet to calculate the Area Media Income (AMI) Percent? I would like to enter an amount into a row and set up a formula that calculates what percentage of AMI an applicant is. I think it would be a long formula that might include all the different amounts of AMI income. Thank you…
I need help with a formula for Schedule Health.
Currently its working in reference to "Todays" date only. I need the formula to take in consideration the "Completed Date" and not turn "Red" or "Yellow" if the task has been completed by the "Target Date". Currently using: =IF([Target End Date]1 < TODAY(), "Red", IF([Target End Date]1 = TODAY(), "Yellow", IF([Target End…
SUMIF and CONTAIN referencing another sheet
Hi Guys, Have got lost in this one, and I think we might be close to cracking it. I am trying to Sum the values from one column referencing a name from separate column, a little like a sales sheet. The complexity is that I am trying to reference another sheet and I keep getting #Unpareseable =SUMIFS({1.Groups WIP Range 1},…
COUNTIFS Issue
Hi all - I am creating a calculation sheet that reference a ticket intake smart sheet. I want to use this calculation sheet to count based on the country that submitted it and what the status of the ticket is. Complete = Complete Open = Not Started, In Progress, Blocked Reference intake sheet: I keep getting #UNPARSEABLE…
Percentage Formula's
I have a beneficiary nomination form I am asking employees to complete, I am giving them the option of 4 beneficiaries, but would like to be able to tally the total percentage amounts they input per beneficiary as they complete the form to a total of 100%. So would be 4 fields/columns allowing percentage inputs, if they…
Formula for Lower control limit.
I am using =UPPER(STDEVP(A:A)) for the Upper control limit but cannot generate formula for lower control limit. Trying to create an SPC Chart for the data collected.
Automation Help
Hello Smartsheet Community, I am trying to figure out a formula for when all subtasks (the children) have been checked that it will automatically add a check mark to the parent. Then, when the parent has been checked that it changes the Account Status to "Account Activated." Is that possible?
Count employee (resource) tasks from second smartsheet if the tasks fall in a date range
I am trying to count the how many tasks a resource is assigned in a project schedule (sheet 1) if they are in a date range (sheet 1). The calculations is being done in second sheet (sheet 2). Example: Peter (resource field is sheet 1) has 10 tasks in a Planned Start date ( Planned start date field in sheet 1). I'd like to…
Help Article Resources
Trending in Formulas and Functions