-
Symbols using formulas
Hi there, I am creating a training matrix, so I have employee names down the left and the training programs along the top. I want to set it so that if for example "WHMIS" expires after 1 year then the symbol would go from green to red 1 yr after the "Training Date" and 30 days before the expiry I would like it to go…
-
Average Days
I am looking to have some metrics set up on a dashboard all around average days to close. I have a column for a sales person, two date columns (one a start date and one a close date) and a column called Workflow Status. How can I do the two following things via formulas: * Filter this first by the workflow status being…
-
Nested IFERROR
I am using the below formula that is working fine: =IFERROR(IFERROR(INDEX({Sales_Parts_1_Description}, MATCH($[Supplier Part Number]@row, {Sales_Parts_1_PN}, 0)), INDEX({Sales_Parts_2_Description}, MATCH($[Supplier Part Number]@row, {Sales_Parts_2_PN}, 0))), "ERROR - CHECK PART #") However, I want to add another INDEX…
-
COUNTIF is double counting rows with multiple assigned contacts
Hello all, I am trying to count rows for one department. This department also has sub-departments. In some rows, it has multiple sub-departments assigned to it. This is my formula: =COUNTIFS({Requests Range 2 - Status}, Category2, {Requests Range 4 - Department}, (FIND("Merchandising", @cell) > 0)) For example, one row has…
-
Need tip for nested formula using IF, AND, and ISBLANK
I have a schedule template with columns for At Risk (red flag on or off), Status (Not Started, In Progress, Complete), and Risk Reason (is it at risk because of Late Start or Late End, or else blank if the flag is off). Here is the Risk Reason we're currently using: =IF(AND([At Risk]@row = 1, Status@row = "Not Started"),…
-
Combining 2 Formulas
Hi There! I have 2 formulas that I would like to combine together. They both work independently, but i can't seem to figure out how to combine them. Formula 1: =IF(OR([Phase Health]3 = "Red", [Phase Health]3 = "Yellow"), "Yellow") Formula 2: =IF((((COUNTIFS(CHILDREN(Status@row), OR(@cell = "Not Started", @cell = "In…
-
SUMIF Formula question
Hello, I am attempting to create a formula that calculates the sum of the Actual Savings column, if the non-applicable column is not checked. The formula I am using is =SUMIF(CHILDREN([Non DH Applicable?]@row), 1 + ""). Not sure why it isn't calculating correctly.
-
Formula to return data from last row of a sheet of data
I am working on a dashboard and I'm using a intermediate sheet to collect data from other sheets of data. Typically the data is entered in each day so I was able to have the formula for most cells to look at the referenced sheet and return the value for the row of data by date =SUMIFS({Incidents YTD}, {Yesterday}, "1").…
-
Contact List column allows multiple entries - using in formula
I am trying to obtain the number of tasks with a status of "Red" by assigned user. The author column only had one individual assigned and the formula =COUNTIFS(Author:Author, ="Jane Doe", Status:Status, ="Red") returned precisely what I needed. I'm trying to replicate the results for the contributors. However, in many…
-
Phase of Work Formula
Hello Community- I am looking for help with a formula to tell me what phase of work the project is in. The formula will be looking at 5 checkboxes in columns. If I complete the last checkbox in the first phase, but have not completed the last 2nd phase checkbox, I would like the column "Project Phase" to return the 2nd…