Formulas and Functions

Archives for May 2020

Need help with an "IF", "CONTAINS" and "OR" formula
Hello everyone First, thanks for your help :) I'm trying to make a formula. I have a cell (cell#1) that has 4 values in it (" one", "two", "three", "four"). I'm looking for a formula that would work like this: If cell #1 contains "one" or "two", then cell #1 =0. Otherwise, calculate the number of values in cell #1. Could…
Display date fields from two different sheets
Hi, I'm tracking an estimated Start and End date (in a Project Sheet) and an Actual Start and End date (in a tracking sheet). In both sheets the rows where the dates are entered are CHILDREN and they both have an Asset # that I'm can match them to each other with. I'm looking for a way to display both the Project Sheets…
IF(OR Statement giving #unparseable, using ; or , ?
Please help. I keep getting #unparseable error for the following formula: =IF(OR([DLExpiration Date]1 > TODAY(); [MECExpiration Date]1 > TODAY()); "NON COMPLIANT"; "COMPLIANT") I want the cell in my Status column to display as NON COMPLIANT or COMPLIANT if today's date is greater than the dates in columns DLExpiration Date…
I get an #INVALID OPERATION using SUMIFS with multiple criteria referencing a separate sheet.
I am having trouble getting this formula right. I want to pull the qty of flooring scheduled to run in a given week (1-52) provided it requires one of 2 types of veneer. (Acacia 1220x077x1.5 or Acacia 1220x185x1.5) =SUMIFS({Schedule File - 052820 Flooring}, {Schedule File - 052820 Week}, Week@row, {Schedule File - 052820…
How do I show health based on end date & % complete?
I'm trying to show health of a project based on how far out from End date + how much of the work has been completed. Green: 100% complete before or on End date Yellow 70-99% and today is < 1 range of End Date <69% and date within 2 days of End Date Red <69% and today is <1 range of End Date This was what I wrote, and I…
Workaround for broken UTC System Created Date Column
Hi, my problem is the same as in https://community.smartsheet.com/discussion/19461/wrong-date-showing-seems-to-be-4-hours-off-the-difference-between-gmt-and-my-time-zone In short, I am on GMT-7 time, while Smartsheet operates under UTC. Because of this, whenever a System Date is generated for entries after 5PM, my sheet…
How do you create a Sum if equation when the criteria is based on a value in a Multi-Select Column
When I chose just one of the selections (I have been using "Product" no" = "or other signs) it only sums the values were there is only "Product" . the equation wont sum if there are other selections included.
How can I create a stacked bar graph that shows different sets of data?
Hi! So I have two sets of data that I would like to graph using a stacked chart. Data set 1 contains a number of actions that are divided by month, for example, the month of May has 5 actions and the month of June has 3. Data set 2 contains the same actions, however, in this set they are divided by source (e.g. deviations,…
Adding Months and Placing Data in a Month Column
Sheet --> https://app.smartsheet.com/sheets/Rp5CHm6mFGcw2CG7x8rFjmfGwVpG394xffJw2x91 I really need some help placing data throughout this sheet by using formulas. I know what I want but I dont know the formulas. I want the total monthly payout to be spread out into the monthly columns once the payout dates are entered. Can…
how do i automatically check a box when 2 other boxes are checked in a row?
When "Connect Done" and "Core/Micro Done" are both checked, I want "Client Done" to automatically be checked. Can someone help me with the formula? I'm guessing its an =IF

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