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
Have a serial number column update when there is a response on another sheet with a new number.
I have an inventory sheet that has a serial number column. I have a serpeate sheet used for tracking advanced exchanges where it populates a column for the old serial number and the new. I need a formula to identify the old serial number from the RMA sheet and match it to the inventory sheet. Then replace it with the new…
Date Formula to return following Monday or Wednesday
Hi everyone, I need help with another date formula. For this one, we have a system entry that needs to be done which will trigger the "Expected live date". I currently have a "Task" column and a "Due Date" column. Here's how it should work but realize the timing may be impossible to capture... 1) If the system entry was…
How to count by month?
I have a column which contains dates throughout the year as mm/dd/yy. Then i have another column for each month of the year and attempting to CountIF in those columns to capture how many requests came in for that month. I have attempted =COUNTIF([Request Date]28:[Request Date]33, "March") yet the cell is calculating 0.
Partial Match
Hello, We have a form that populates to a Sheet 1. The employees enter general information, except for their employee number. We have Sheet 2 that has the employee# and so we are able to pull in the employee # into Sheet 1 using Match. =IFERROR(INDEX({Master Non-BVS User ID}, MATCH(Email@row, {Master Non-BVS Email}, 0)),…
Update Parent Row based on Child Rows
I am trying to update Parent Status based on the Status of child rows as stated in the below If any child rows have a status of In Progress, set parent row status value to "In Progress" If ALL child rows have a status of Complete, set parent row status to “Complete”If the number of child rows with a status of Not Started…
Crossing Multiple Colum's on a Different Sheet
Good afternoon, I am stuck trying to gather data to build a dashboard with the appropriate information. I am trying to get a formula that compares which department is selected from a drop-down column (Dept Requesting). I then need this information to be compared with a different drop-down column that displays status…
Exclude one child row in the parent row update
I have created sheets to track critical HR tasks that are performed on regular basis. All is working as expected (thanks to help from a few of you) but this morning was thrown a curve ball. USE CASE: This one tasks has ten check points in process. Of the ten, the last step is only late 4 business days after the previous…
Count if function and sequential numbering
I have been banging my head trying to get this to work. I am trying to get an auto number that goes 1-….., for each name that appears in another row. Ex. Jim shows up 5 times this each time would be numbered 1-5, then Jordan shows up 8 times and would be numbered 1-8. So on and so forth. Any help would be appreciated.
Using Sheet Summary Field in a Workflow Alert
Hello - Is it possible to set up a workflow alert on a sheet summary field? I would like to be notified when a sheet summary field is updated. Thanks
Formula to Auto assigned Id_Number as Input in Sheet
What Formula should I used get desired JV number output -> Here In Sheet I what to get out similar as shown in JV Number column, Point to be note number id in JV number should be assign as per entry in sheet. 1) So Here in variation# column the entry is against variation num. VAR-0012814 so number assign to it should be…
Help Article Resources
Trending in Formulas and Functions
Flagging Duplicates with Multiple Criteria
Hi all, Trying to identify when I get a duplicate that meets both criteria, when the Work Order Number is the same and when the Service Area is the same. I'm using the following formula, =IF(COUNTIFS([Pass Number]:[Pass Number],[Pass Number]@row,Site:Site,Site@row)>1,"Duplicate","Not Duplicate") But its not working…
Percentages as progress bars
Hi we are using the below formula to calculate a percentage which is then reflected by a progress bar, when the overall percentage is at 49% the progress bar is showing as "full" instead of reflecting the criteria of the formula underneath =IF([Overall %]@row < 0.25, "Empty", IF(AND([Overall %]@row >= 0.25, [Overall %]@row…
Hello! How can I work an OR statement into this formula? I need it to look at 6 different columns.
=IFERROR(INDEX(COLLECT({Provider being reviewed}, {MRN 1}, CONTAINS(@cell, MRN@row)), 1), "")