-
Countif Function Question
I am stumped on this for some reason. I have a column called "Past Due" that is a Symbol column. I am looking for all tasks past the due date using this formula, =IF(TODAY() > [Target Date for Resolution]@row, "Red"). This works fine. My issue is when I try to count how many past due tasks, or red circle symbols I have in…
-
Copying a Sheet & Using Formulas Changed the Form Attached to Original Sheet
Hi SmartSheet Community, I came across an issue with copying a sheet and the form that was attached to the original sheet was modified from using a formula in the copied sheet. Original Sheet Name: IHA Copied Sheet Name: Copy of IHA INDEX/MATCH Sheet Name: Job Model After I copied the IHA sheet (File --> Save as New), I…
-
Summary Formula
Hello, I am using Smartsheet for project planning. I would like to track adherence to plan, specifically counting the average of the Start to Plan and Finish to Plan and represent the number separately as a percentage so I can see what percentage we start and finish to plan when it happens. I have created an Ancestors…
-
Is there a formula to get values from B1 if A1 is blank?
I want a formula that will return with the value of cell A1 but if cell A1 is blank, then it will look at the value in B1 and Then I want to do an Index match to a different reference. This is what I have so far: =INDEX({Equipment Master Range 3}, MATCH([Asset Number]@row, {Equipment Master Range 4}, 0))
-
Need help with COUNTIFS in sheet summary utilizing 2 columns from the same sheet
I have a sheet with Start Date (date properties), which I have converted to Current Year (text/number properties) in a separate column and then another column called Fully Vaccinated? with dropdown single select properties. In the sheet summary, I have the following formula: =COUNTIFS([Fully Vaccinated?]:[Fully…
-
Counting Unique Values Under Parent Rows
My sheet current has four project types (parent row.) Underneath each project type is the project name (children.) And underneath each project is a list of the team members working on that project (ancestors.) I need to be able to count each unique employee working on projects under each Project Type. So if an employee is…
-
Index/Match formula inconsistent
I have 2 simple index/match formulas to bring over information from one sheet to another. However sometimes the formula returns a #NOMATCH, seemingly dependent on how the data in the data in the other sheet is sorted? There are not parent/child rows so I don't understand why having the rows in a different order would…
-
Partial result
Hello, I'm attempting to create a way to review an account number from one cell, and have a different cell populate just the last four digits with X's for any other remaining characters. So account 1234567 would show XXX4567, where as account 123456789 would show XXXXX6789. Fairly new to complex formulas and not sure where…
-
Formula to include weekends & Holidays in count but exclude weekends as a date for the result
I'm converting a file from Excel to Smartsheets for Quarterly, Semi-Annual, & Annual inspections. I want to set the first inspection date up and then have formulas in place to auto assign the remaining inspection dates for the next 2-3 years. I'm using WORKDAYS with an if formula which is providing a result but it's not…
-
Find "soonest" or closest date in a column of dates.
I'd like to find the earliest or Next available date for 3D Printing Dashboard. I'd like to use IF or IFS given that I would like to search only rows with a "Printing" Status. There are no formulas that seem to help. Any help would be appreciated