-
COUNTIF Formula Providing Incorrect Result
Hello! I am adding sheet summary fields to count the rows for each status. I noticed that the results I get with my COUNTIF formula are incorrect. The correct results would be: Completed - 3, In Progress - 1, Not Started - 2 (this one is correct) I tried this formula on a second sheet and still received an incorrect…
-
Nested COLLECT question
I need help with creating the formula for the [PrevHours] column (below). The expected logic for this formula would be: for a given row's ColValue, find the max date from all instances of ColValue, then find that row's Col2Value. For example: Project | Date | Hours | PrevHours XYZ | 5/1/23 | 10 | "" XYZ | 5/2/23 | 15 | 10…
-
Copy duration value to backup field and restore on specific status
I use a standard project template when beginning each project. As requirements are reviewed and tasks are not needed we mark the status N/A and zero out the duration. I'd like to be able copy the duration to a backup field and should the status change to anything other than N/A or Complete copy the backup duration back to…
-
Checkbox Formula: If Due Date has passed but completion date is blank
I have two columns: 1: A "Date Calc" field (contains a formula) that automatically displays a due date for a task based on manually entered dates from a prior field. This is a date column. 2: A Date Complete field that is manually entered. This is a date column. I have added a third column that will automatically check a…
-
Sum of children if it has no descendants
I am attempting to create a single cell formula to capture the total value within a budget sheet, however =SUM(DESCENDANTS()) is capturing data multiple times and doubling costs due to children rows being routinely being summed (=SUM(CHILDREN)) by staff for reconciliation purposes. The sheet layout has a parent row with…
-
Index Collect Max | Get the most recent item based on Created Date
Im using the formula below, but getting an error #INCORRECT ARGUMENT SET =INDEX(COLLECT([Current Note]1:[Current Note]7, Auditor1:Auditor7, Auditor@row, [Created Date]1:[Created Date]7, MAX([Created Date]1:[Created Date]7)))
-
Need to Figure out # of days each emplyee is on leave in a given month
Hi Everyone, I'm working on a work days calculation for a given month, for that I need to calculate the leave days for a given month. Below is my grid. Orage colors columns is in different sheet - Leave requests are updated here and green color columns is in different sheet - Need to figure out total # of days on leave in…
-
Pre-Approved Changes Pulled From Approved Standard Changes
Hello! I'm attempting to create a change management system wherein the pre-approved changes can reference previously submitted (and approved by our CAB) standard changes. Pre-Approved Changes - need it to be a drop-down list of pre-populated requests that are Standard change requests that have been approved to become…
-
Workaround to show date for grouped items
Is there a workaround to show the date in the report? I want to show the data in one line but I don't want to group data and collapse it, I want to show Name, Total price, and created date as one line item if the name matches
-
SUMIF Different Columns
Hi, I have two columns [Initial Priced Amount] and [Final Account Amount], what I am trying to do is use the amount in [Initial Priced Amount] if the [Final Account Amount] is blank, but if it isn't blank then use the amount in it, the [Final Account Amount] column. I want to use it in a couple of formulas in other columns…
