Formulas and Functions

Archives for February 2020

Is it possible to add dynamicaly a line ina summary sheet if a new sheet is created in a folder?
Hi there, i manage several projects. Each project is in a individual sheet. I created a summary sheet to gather information from projects. When i create a new project in a folder, i wonder if it is possible to add this new project inside the summary sheet dynamicaly as a new line? I tried the report, i just have to click…
Excluding Multiple Options
I am having a real challenge with a particular function. I'm thinking someone will know this right off the top of your head. =SUMIFS({Digital Marketing Workflow- Total Hours}, {Digital Marketing Workflow- Priority}, <>"Sub-Category", {Digital Marketing Workflow- Project Cat}, "Advertising: Concept") I am trying to exclude…
IF Statement help
So, I am trying to create a column that adds the Student ID, when the Student is Named. This way I can make my reports de-identified. From a workflow standpoint, Teachers use a drop down for the Names on a Form and then enter Student Progress notes. I want a column next to it the Student Name that inserts the Student ID.…
Formulas for calculate number of days depending on list of value
Hi there, I need to know if it is feasible to obtain this formula : In a sheet, i can choose several option from a list of value ( checkboxes). If option A is checked, i add 1.5 days for ex. If i choose B, i add 1 day. If i choose A & B, i obtain 2.5. And so one. How can i create that in one formula? Any idea? Many thanks…
Using VLOOKUP for Approval Requests Across Sheets
GOAL: 1) Have a secure and error-free VLOOKUP on Sheet B and 2) Run a multi-level approval request automation on Sheet B. DETAILS: I have 2 sheets. Sheet A tracks contracts. It has columns for individuals (contacts) who are designated approvers for changes on contracts. Sheet B tracks providers named on the contracts to…
Changing % complete using a formula in another cell
I know you cannot use a formula in a "% Complete" field. Is there a way to update "% Complete" from a formula in another column (something like a "helper" column)? I have a "Task Status" column with drop down values of Not Started, In Progress, and Complete. I would like to change the value of "% Complete" when I change…
How to find difference between 2 rows, but only if data exists in both rows?
Hi, I'm trying to make a formula that says 2020 Experiential Audit minus 2019 Experiential Audit = value in Experiential Audit YOY change...but only if there is data in both 2019 and 2020. See my sheet below. I think this is an IF formula but I dont know how to say If there is a number... Thank you in advance!
Can I lookup a value based on two criteria?
Hi , I have two sheets, sheet 1 holds fixed data and sheet 2 is populated via a form. I want a cell in sheet 2 to return data from a cell in sheet one when two column values in sheet two are matched to two column values in sheet 1. Is this possible? Hope this makes sense.
Duplicate value formula
I am trying to use a formula that will place a check mark in my duplicate check column if an invoice# in column 8 is a duplicate. I was using the =IF(COUNTIF format but it doesn't seem to be working for me Any recommendations This is the formula I use & I keep getting an error of #UNPARSEABLE =IF(COUNTIF([Invoice…
Formula for % of checked boxes
I'm requesting help to create a formula that will calculate the % of the checked boxes that are checked vs unchecked for calendar year with a project status of complete?

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!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

Watch video

Trending in Formulas and Functions

IndexCollect / JoinCollect for Multi Select Columns
I have one column called PRIMARY PRODUCT which is a single select dropdown and another called PRODUCTS OTHER which is a multi-select drop down. I have a third column called PROFIT CENTRE CODE which needs to populate based on all the products selected in the products columns. I have worked out a formula for PRODUCTS OTHER…
Count Active assignments by individual
=COUNTIFS([Assigned To]:[Assigned To], ("Name"))AND [Request Status];[Request Status], is one of "In Progress", "Submitted", "Completed") My countifs statement works by name but I want to further refine this by [Request Status];[Request Status], is one of "In Progress", "Submitted", "Completed")
If then formula help
Hi All! I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the initiative’s average score is between: 5 and 9 it is considered a project and if the score is between 10 and 15, it is considered a program. I…

Formulas and Functions

Discussion List

Help Article Resources