Formulas and Functions

Archives for May 2022

Can a form field require a specific number of digits to be entered
Is there a way (similar to Excel data validation) to require seven digits to be entered into a forms field? Each individual completing the form has a unique ID number which is seven digits long. Currently the first three or four number of the ID number are zeros. Each time an activity is completed the individual completes…
Return value if a cell in the same row has highest value in the column
I'm working on an email campaign and want to see which issue has the highest open rate. If a value in Column C has the highest value in the column, return the value of Column A in the same row. I'm stumped. Can anyone provide this formula? VLOOKUP seems like the obvious choice, but I don't know how to reference a MAX…
Can you make a formula lookup by number and name?
If employee only know name can they type in name and get project number and vice versa?
Request an Update
Hi I'm using the "Request an Update" function in automation and this works ok, I've noticed though that due to no fields being mandatory that a user can just submit the form without making any changes. The snag here is that I have another automation function setup based on a row change but this won't fire if a user submits…
Show Red Ball if Past Due
I have a formula in the "Health" column of a sheet. This formula shows a colored ball depending on the "Status" of another column. The "Status" column is determined by another column, "% Complete". Ultimately, the part of these formulas not working is that I want it to be a Red Ball if the End Date is past and it has not…
IF Statement to check a box
I have two Columns they're: 1.) Level (NUMBER) 2.) Don't Include on Dashboard (CHECK BOX) I need the check box to be checked if "Level" is is not blank
'Assigned To' Formula Quirks/Advice
I am building a large Control Center solution for a customer that contains 20+ schedule templates that are being used to manage different portions of the workflow. In the templates, there is a "Department Owner(s)" multi-select column that reflects assignment of the associated department (PM, Editorial, PRD, etc.). Then,…
Edit formula to return a blank result if the result is zero
Can you tell me how to edit this formula to return a blank cell instead of a zero? It is putting zeros on our dashboard graphs/charts that I want removed. thanks!! =COUNTIFS(Header:Header, 0, Status:Status, "Yellow", [Current Phase?]:[Current Phase?], "1", Level:Level, "1")
How to form a =COUNTIF Column in reference to an already formulated 'Unique Column'
To further explain in reference to this community post. here, https://community.smartsheet.com/discussion/57396/generate-list-of-unique-values-index-distinct-vlookup/p1 I've successfully been able to apply the formulas suggested from that post and build out a column of unique names. Now in a separate column I'd like to be…
Root mean square error(RMSE), statistics
HI smartsheet team, is there any possibility or functions for square root and descirptive statistics in Smartsheet? if yes please share some literature?

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Can I copy a row to another sheet more than once?
Is it possible to copy a row to another sheet and have it copy multiple times on sheet #2? For example on sheet #1 I have a project with multiple dates for different deliverables within the overall project. I need these projects to copy over to a 2nd sheet where we get a separate line for each date. Right now we are…
Trying to determine if all children have N/A Status
I'm working on a formula for summary rows to look at the % Complete of the row as well as the statuses of the child rows. I have the % Complete portion handled: =IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), "In Progress", IF([% Complete]@row = 0, "Not Started"))) I want to add in…
I am trying to manipulate my SUMIFs Formula
When Using SUMIFS(Range, Criterion Range 1,Criterion...) I have multiple columns for example Estimate Spend, 25% spend, 50% spend, Rather than having multiple %'s of spend I just want to be able to use 1 reference and multiply it by 0.25 or 0.5 in the first "Range" category so =SUMIFS({Spend} * 0.25, {Phase}, "phase 1",…

Formulas and Functions

Discussion List

Help Article Resources