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.

Counting a Unique identifier in a column
Hello SS Community, I am halfway there but seem to be doing something wrong- I want to count how many times a Person shows up in the list and record the number. I have this formula =IF(COUNTIF([Person Number]:[Person Number], [Person Number]@row) > 1, 1) and it is working perfectly to record when the person number in the…
Why does the SUM function always deliver the value "0"?
Hello everyone, I have integrated my smartsheet account with a formbuilder service (123 formbuilder). Smartsheet perfectly shows and updates all data which I gather through my various forms. Integrating all data into one giant mastersheet works fine as well. But I am experiencing a small problem with the SUM function.…
Countifs with multiple choices in one row AND one choice in another row
Greetings: Im trying to narrow down a count based if one of several choices in one column PLUS only one specific choice in another column =COUNTIFS([Status:Status], "Discharged") + COUNTIFS([Status:Status], "Lost to Follow-up") + COUNTIFS([Status:Status], "Follow-up completed") + COUNTIFS([Status:Status],…
Counting Formula for status and > date.
Greetings, I'm trying to create a formula that counts if the status is "Completed" and the "Completed Date" is greater than 12/31/20. Does anyone have an example I could use?
Count Ifs Formula Multiple Criteria
Hi all, I'm hoping you can help me! I'm trying to make a formula that counts unique email addresses (on another sheet) but only if they attended an event (marked in my sheet as 1 for attended, 0 for no show). This is what I've tried with no luck: =COUNTIF(DISTINCT({WFW AUT Raw Data Range 1}, Attended:"1")) 'Data Range 1'…
Mimic name manager (excel functionality) and logic in Forms
I have a sheet with a column that contains a column formula to index, collect a response from a "metric" sheet. I would like to include this column as a hidden field in a form so that I can apply conditional logic to then display the corresponding column and dropdown list for the user to select. This is a workaround…
Average If by Current week
I'm trying to figure out the formula to calculate the average of a column based on the current week. I do have a week column. I tried this but it didn't work :( =AVERAGEIF([Created Week]:[Created Week],TODAY,[Overall Satisfaction]:[Overall Satisfaction]) And then... I'm also looking for the average for the PREVIOUS week.
SumIF from another sheet
Hi, I have a formula that sums up the expenses for a particular billing code, like this: =SUMIF([Account Code]:[Account Code], "539023", [Expenses Submitted]:[Expenses Submitted]) This works well, however, I want to use this code on a different sheet, without having to link to a bunch of cells on the first sheet. I can't…
Formula/Summary Function
I want to be able to summarize how much our locations are requesting in funds, can someone help me with a formula? Column A - Month Column B - Date of Request Column C - Amount Column D - Agency (single select dropdown) Column E - Use of funds (multi select dropdown) Ideally I'd like to summarize column C, by column D -…
Row-Average of Percentages in cells
Hi, I am trying to do the average of % (data in percentage) in a row. Few of my cells are blank too. Rest all data is in % format. Formula i am using is avg(selecting the rows) Error getting is #Divide By Zero Please help

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