-
How to sum across multiple columns
Hello, I'm looking to calculate across multiple columns and have the formula total it in the end column but I can't figure out the formula to do it, I keep coming up with #UNPARSABLE. I've attached a snapshot of the sheet
-
% Complete Formula based on Start/Finish Dates
Hello! I am in need of help writing a formula to auto-calculate the "% Complete" column based on the "Start" column date and "Finish" column date. Additionally, I also have an auto column for "Duration". The goal is to have the % Complete auto calculate based on the today's date in relation to the start and finish date…
-
Help With SUMIFS Formula
Hello, I'm trying to retrieve SHIPPING COST for the current month of DATE REQUESTED for all repairs under ACTION TAKEN for the WARRANTY ACCOUNT “41130”. There are 4 different columns I’m trying to draw data from and I’m not too sure how to sequence the formula. All caps words are the columns I’m trying to include in the…
-
Please help with the following formula to return '% Completion planned' with conditions:
I have the following columns for tasks: Planned start date Due date Planned duration; calculated as =1 + ([Due date]@row - [Planned start date]@row) Days remaining or overdue; calculated as =[Due date]@row - TODAY() % Completion planned; calculated as = 1 - ([Days remaining or overdue]@row / [Planned duration]@row) I am…
-
Index Collect Permissions
Hi all, I have a Smartsheet which looks up values from another sheet using Index Collect, which is working well. However other users with Smartsheet license and with a free license will be loading the sheet who don't have access to the source data sheet. I don't plan in opening the this (the destination sheet) hoping that…
-
Date formula for Last month, Last Quarter, Last year
I'm looking to add some formula to my summary sheet and the requirement is for analytics for Last month, last quarter and last year - so excluding current month Examples if: current month = May 2020 last month = April 2020 last quarter = February, March & April 2020 last year = May 2019 to April 2020 inclusive These would…
-
How to mark column with 1 or 0 if the month is in a date
Hi, I am trying to track whether or not someone has been active in a month based on their Last Login date. I created the formula to mark a cell as 1 if the month is in the Last Login date or 0 if it is not: =IF(MONTH([Last Login]@row), 1, 0) But the problem is it gives an #INVALID DATA TYPE error if it hits a blank date.…
-
How do I count 2023 Q1 items that are not specific request statuses?
Here is my formula: =COUNTIFS([Plan of Record]:[Plan of Record], FIND("2023", @cell) > 0, [Request Status]:[Request Status], NOT(CONTAINS("Cancelled", @cell), NOT(CONTAINS("Merged", @cell), NOT(CONTAINS("Rejected - Insufficient Value", @cell), NOT(CONTAINS("Rejected - Duplicate", @cell)), [Planned MVP Launch Date]:[Planned…
-
INDEX, DISTINCT, and MATCH formulas
I am trying to put together a list of our project managers and which business units each of them are responsible for. I need it to be in a certain format because right next to each of the business units, I am going to have 3 COUNTIFS formulas to count different things based on the project manager and business unit. I…
-
Collect and display associated numbers
Hello, I'm looking for a formula to use to reference another sheet and collect and display the different User Story numbers associated with and OMNI #. I was thinking maybe VLOOKUP or INDEX with COLLECT or MATCH but I can't get it to work. Here is the data set: So if the formula works correctly, the row with OMNI002800…
