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.

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

autofill of formulas is not working when adding more than one line of data without saving the sheet
I have a sheet with some formulas. If I add a new row of data ,the formulas are autofilled as expected. If I add another row of data, the formulas are not autofilled. If I save the sheet after adding the first row of data, the next new row will autofill the formulas. This means, I have to save the sheet after adding each…
Vlookup with multiple rows of same search value
I am trying to convert an excel sheet that has multiple rows of the employee's name with one performance objective per row. So we have this: Sue Smith/Objective 1 Sue Smith/Objective 2 Sue Smith/Objective 3 I want my Smartsheet to be like this: Sue Smith/Objective 1/Objective 2/Objective 3 Trying to wrap my head around how…
Average Health Bubble from Multiple Project Health Bubbles
Hi All! I have a portfolio dashboard for my PMO team and all of their projects. I use a metrics sheet to drive calculations for all projects. I am stuck on one thing and I need some help! I have formulas to calculate the main health bubbles for each from R, Y, G these come into a count: What is the best way to average…
Creating a Summary Table based on Client Work and Resource Name
I need to get a summary report to show the resource name against both the client work and non-client work with total hours Example results: I can get the totals using =SUMIF([Resource Name]1:[Resource Name]17, [Field 1]#, [Hours - Client]1:[Hours - Client]17). Can I use Index/Match to get my summary table, please advise?…
HOW TO SOLVE INDEX MATCH WITH DATE TENURE?
This is Table 1 This is Table 2 The criteria is Match the Date & Name from Table 1 to find the Amount and put it in Table 2. And also add the amounts which is under the date tenure for the specific month. After many research I have found a formula which can mach 2 criteria at once but its not working for add the amounts…
Is there a way to show the date/time of the cell update in the adjacent cell?
I am sure so many of us are looking for this solution. Most of the common workflows have due date. Based on that a timeliness report is generated. I tried using the "Modified Date" Column to calculate the timeliness of the tasks (rows) but these just update if columns are updated. Is there a solution to this? Thanks,
Sheet Summary Formula #Invalid Data Type
I have a countifs formula that is returning #invalid Data Type error in the sheet summary. The reason for the error is that I have blank cells in the finish date column range. How would I add an iferror or something similar to the following formula to cause it to ignore the blanks? =COUNTIFS([Renovation…
Averaging by Resolution Week
If an issue has the label 'jira_escalated', I'd like to calculate how long it took to be resolved. (Solved in WorkDays column) I have the columns week number (W#) and Resolution Week (RezW#). How do I get an average time to resolution by week? How do I calculate 4/8/12 week rolling averages?
Flag Item if cell is blank a the date is greater then 3 days
Hi, Not sure what I am doing, probably something really obvious, but I am trying to flag items that have a cell blank and the date that person was notified is more then 3 days from today. Could someone please assist? I greatly appreciate it! =IF(AND(ISBLANK([Approver (Type or Email)]@row, [Date Initial Email Sent to…
Flag at Risk column based on child rows summary
I have an "At Risk" column where the flag is triggered when a certain condition is met. Can anyone please suggest with a formula for each parent row in the at risk column such that if the child rows have more flags raised than blanks then the parent row cell needs to also be flagged up? So say if there are 6 child rows for…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Get a Countif on values in a multi-select column of cells
I am trying to write summary formulas to determine how many values appear of each in the drop-down of a column. I tried adding a wildcard, I have tried HAS, and tried CONTAIN, but I'm still not getting the right values. Currently I am getting 0 as a return value on this formula: =COUNTIF([ROI Type]:[ROI Type], HAS([ROI…
summaries in report
Hi i'm trying to build a report with summaries options. i have two column that i want a Min and Max summary. the column for the Min i a texte column and works well. but the other column has formulas and the Max doesn't works. there is a way to fix that? (instead of creating another text column in the source sheets?) here…
Converting email addresses with hyphenated First and/or Last Names
I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one. I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags