-
Formula to Use in Multi Select Column
Hello! I have a column that is set up as a multi select with four options in the drop down, and I would like tally of the total number of times each option was selected. So for example, if my four options were Apples, Bananas, Peaches, Grapes, and people are able to select any or all of those options in a single cell, I…
-
Using COUNTIFS with OR function spread across multiple ranges
Hello, I am trying to build a COUNTIF function that will count all cells which meet criteria 1 and either criteria 2 or 3, which are found in different ranges. Example: In Sheet1, I have the following columns: shape, color1, and color2. In Sheet 2, I would like to create a function which counts all observations in Sheet1…
-
What is the formula to subtract today's date from due date column so I can see remaining days
-
Formula to count In Progress
Hi, can anyone help with this formula? I need to count the children in a smart sheet project and not include the parent or grandparent as they have formulas that alter based on the overall % of the project as a whole. I have helper columns that indicate level, and I need this to ignore the summary count from 1,2,3 etc...…
-
SUMIFS + Contains
I am trying to do a SUMIFS + Contains and keep getting #INVALID DATA TYPE. Any help would be appreciated! =SUMIFS([ARR Year 1]:[ARR Year 1], CONTAINS([Non DOS Solutions]:[Non DOS Solutions], "CDM"), [Opportunity Close Date]:[Opportunity Close Date], >=DATE(2022, 3, 1), [Opportunity Close Date]:[Opportunity Close Date],…
-
IF / Then Formula with Dates
I'm trying to create a smartsheet formula for the below information.... If the Form Date Field is 6 weeks(42 days) or less than the event Date then Event Approved = NO. If the Form Date Field is 6 weeks(42 days) or more than the event Date then Event Approved = YES. Thank you
-
Fiscal Year Calculation
Hello, I am trying to have a column that calculates a fiscal year automatically based on whether the parent Completed Date falls before or after July 1. Something that follows this logic: If the parent "Completed Date" month is less than 7 (July) display "FY 'Parent Year'", otherwise display "FY 'Parent Year + 1'", if…
-
Formula to return 'TBA' if cell contains text 'TBA'
Hi, I'm currently using this formula which works: =IF([\[H\] Variance (%)]@row < 0, "Under Budget", IF([\[H\] Variance (%)]@row = 0, "On Budget", IF(AND([\[H\] Variance (%)]@row > 0, [\[H\] Variance (%)]@row < 0.15), "Moderate Budget Overrun", IF([\[H\] Variance (%)]@row > 0.15, "Significant Budget Overrun")))) However, I…
-
Help with conditional ongoing merging of multiple spreadsheets
I have 3 sheets. 2 are kept up to date with new activities by different group members. the third I want to capture new entries into the other 2 sheets when certain conditions are met. Is there a way to write a lookup to search for new entries and if found add to third sheet with accompanying information? The data on the…
-
Ancestor Formula for RYG
Hello, I am trying to create a Status for the Overall Project using the Ancestor formula based the Parent-Child status. It is NOT based on %. I cannot seem to make it work. It works well for the Parent-Child but then when i try to go a level up it is Unparseable :( This is what I have at the Parent level…