-
Nested IF OR AND string
I am looking to combine 2 variables supported by an AND statement to look for matches and then result in an output REGION AREA Owner (output on match) =IF(OR([::IMPACTED REGION(S)]@row = "US", [::IMPACTED REGION(S)]@row = "CAD", AND([::IMPACTED FUNCTIONAL AREA(S)]@row = "Insurance", [::IMPACTED FUNCTIONAL AREA(S)]@row =…
-
Sheet summaries OVERALL average
I was able to resolve a #DIVIDE BY ZERO Error message by using this formula =IFERROR(COUNTIFS(I:I, 1, C:C, 1) / COUNT(Hinge:Hinge), 0). However, I am currently hitting a road block on getting an accurate average from my sheet summaries. Is there a way to get an average of all summaries not labeled OVERALL that will only…
-
Multi-Formula Help
Hello, I am trying to use two columns compared to two columns in another sheet to produce the value of a third column. For example, I need to produce the annualized revenue value if the client name and team member match. So I have one master sheet with the client name, team, and annualized revenue amount, then another…
-
Count if with dates
I am trying to count new hires for 2022. My formula is =countif({Hire Date},=>Date(01,01,2022)), but it comes back unparsable, any ideas?
-
Adding an IF statement to a formula
I'm using the formula below to subtract numbers in two columns and provide the answer in a third. Some of the cells in the first column don't have numbers (there are words) so I am getting an #INVALID OPERATION error which I expected. I want to add an IF statement to the formula to return "0" in place of that error (not…
-
Displaying first entry and hiding duplicate names on project plan sheet
Hello, @Paul Newcome You have been extremely helpful to others in the forum, so I am hoping to reach out and work with you. My objective is to have team member names displayed in a rollup/dashboard without excessive manual data entry by project leaders. My thought is that on the project plan sheet, the project leader must…
-
I want to use vlookup to pull data from 1 sheet to another, but it isn't working, & I don't know why
Sheet one houses invoices. Sheet two houses statements. I want to be able to pull invoice numbers from sheet in three different rows into one statement row in sheet two. So, one cell in a row in sheet two under a column titled "Invoice Number" would contain 3 or 4 invoice numbers pulled from sheet one. My formula always…
-
Formula for percentage of tasks completed by week?
Hi, I'm currently working on a sheet where I would like to correlate a percentage of tasks completed with an individual week number so I can create a line chart to plot % of tasks completed by week. Currently my formula is =COUNTIF([Week Number]1:[Completed within Scheduled Frequency]16, ="Percentage Complete") but this…
-
Adding to RAG Formula
Good Day All: I am Trying to add a Gray Harvey Ball to Task that have Not Started and have 0% Complete Plan and 0% Complete Actual. Current Formula: =IF([% Complete (Actual)]@row = 1, "Blue", IF(TODAY() > [Finish Date (Actual)]@row, "Red", IF(TODAY(10) >= [Finish Date (Actual)]@row, IF([% Complete (Actual)]@row < 0.75,…
-
Split Text to multiple Column Formula not working?
I tried to split the text in Course Release date to two column if there is more than two dates. For Course release date original column, I used =LEFT([Course Release Date]@row, FIND(" ", [Course Release Date]@row)) For Course release date if refreshed column, I used =RIGHT([Course Release Date]@row, LEN([Course Release…
