-
How do I amend this formula
What should this formula look like if I also want it to ignore any tasks that do not have an end date? =IF(AND([End Date]8 < TODAY(), NOT(Status8 = "Complete")), 1, 0) Its template formula from a project template set that surfaces at risk tasks to the roll up dashboard. IE those where the end date is i the past and the…
-
Use Max Collect to find most recent record with value in multiselect columns
I have a sheet with 2 multi-select columns. The value options for the 2 columns are the same, and are listed in a 2nd sheet. In the second sheet, I'd like to write a formula that finds the most recent date where the value is present in one of the two multiselect columns. Once I have that data, I'd like to know which column…
-
Checked box applies formula to another row
Hi, Is there a way for when a box is checked to have the formula below automatically apply to the Health column? Basically, we only want Critical Path items that are checked for the below to apply to. =IF(Status@row = "Completed", "Blue", IF([Estimated Completion Date]@row >= TODAY(), "Green", IF([Estimated Completion…
-
Creating an At Risk Formula
Hello Community, I am trying to create a formula that will create a flag if the tasks are not marked as Completed in the check box column and the Due Date is within the next 3 days. I've made several attempts at using an IF/AND formula, but can't seem to get it to work properly. Any assistance you can provide would be…
-
#CALCULATING Error - Incorrectly Stating Over 100000 Cells Referenced
Good morning, I am receiving a #CALCULATING error for the below formula: =COUNTIFS({Mobile Unit Data Form Range 15}, =1, {Mobile Unit Data Form Range 3}, "EMPLOYEE", {Mobile Unit Data Form Range 1}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) Range 15 is a checkbox, Range 3 is…
-
How can I get the sum of one column meeting two conditions
Trying to get multiple conditions and sum up total billable. Where Manager = John Smith in one column and Invoice Date = January in another column Getting the #UNPARSEABLE error =SUMIFS(AND(Manager:Manager, "John Smith", Invoice Date:Invoice Date, MONTH(@cell) = 1), [Billable Total]:[Billable Total])
-
Formula Help - Remaining Hours To Be Worked In A Term, Equally Spaced Out Monthly
All, You've always been good to me. I'm hoping to get across the finish line for a formula I've been working on. Here's what I have: * Start Date, End Date, Working Days within that range (could be one week, could be ten years) * Remaining Hours (total hours necessary to be worked within the Start & End Date) I need a…
-
Comparing multiple data sets
Hi all, I'm attempting to compare data sets between 3 different sheets. Basically I want to compare our internal data to the clients data. The common denominator between all 3 sheets is a claim number, the dates in the columns though will likely be different, and my goal is to identify which claim numbers have dates that…
-
Index Match dilemma
I am attempting to access info from another sheet. I have a list and need to attach the correct person to the item. I've tried 2 different formulas, but it's only bringing back the 1st person that it found, not a different person for each item. Here are the 2 formulas that I've tried. What am I missing? =INDEX({TO CHECKOUT…
-
Status Formulas
Hi. First time here. Sorry if this is not the correct avenue. I need a formula for a "Complete", "In Progress", and "Not Started". If start date is blank then the status should be "Not Started". If there is a start data then it should be in progress. If the completed date is entered status should be "complete"