-
Use of Coloured Symbols
I have two columns that use formulae to calculate whether a Task is on budget based on the hours worked over the Revised Hours. The first column uses RYG balls whereas the second column uses RYG 3-state arrows. The first column displays the coloured symbol but the second column displays the colour as a word. I have tried…
-
Using a formula to Reference another sheet with two variables
I'm trying to create a formula for a report that references another sheet but it needs to reference and check two columns. My crude formula below to illustrate: =COUNTIF ((RefSheet1Range1)="Windows Server" AND (RefSheet1Range2){Status}="Completed") This would return a count of the number of Windows Servers that have been…
-
Help with a SUMIF formula and a reconciliation spreadsheet
I am trying to build a reconciliation spreadsheet that compares two sets of data. Pictured below: The first set of data with the "QBO SKU" is pulling the information correctly using the below formula: =SUMIFS({QBO – shipment history (7/30/20) Range 1}, {QBO – shipment history (7/30/20) Range 2}, Class@row, {QBO – shipment…
-
Ignoring Blank Cells
How do I make this formula ignore blank cells? I want it to total the percent of YES cells to NO in a range. =COUNTIFS(CHILDREN(), OR(@cell = "YES", @cell = "No aplica")) / COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> ""))
-
COUNTIFS, WITHIN DATE RANGE, ISBLANK FOR ADDITIONAL DATE RANGE
I need to write a formula for counting active projects that are past due for a current month based on schedule complete date being in that current month and when actual complete date is blank. Project Status = Active Schedule Complete Date = 7.1.20 - 7.31.20 Actual Complete Date = Blank Here is the data and fields I would…
-
VLookup is not bring back values
I am using vlookup in a master tracking sheet that is looking up Names and bring back date values in a data entry sheet. The data entry sheet is updated on a weekly basis from the same group of Names multiple times during the week so the formulas is suppose to bring back the dates activities are completed with rows added…
-
What is wrong with my formula?
I'm trying to do multiple IF formulas to show a Green, Yellow, or Red traffic light based on a % complete column. I can get "Red" (<70%) to show by itself, "Green" (100%) to show by itself, but I can't get the yellow range or all three together. What is wrong below? =IF([% Complete]3 = 1, "Green"), =IF([% Complete]3 <1 AND…
-
Sumif in a RYG symbol column
Hello, I have a formula that returns a Red, Yellow, or Green icon depending on criteria in another column. Red, Yellow or Green correspond to a priority in our work order flow. Then, in a metrics summary sheet, I have a Sumif formula based on the status (Red, Yellow, or Green). At times, we need to override the formula,…
-
Multiple IF functions Nested
Hello, I would like to Nest two If functions in one cell. Below is what I have, but I am receiving an error #unparseable. =IF([Sales Person 1]2667 = "Y", ([Net Profit]2667 - 100) * 0.5, IF([Commission due]2667 > 0, "0" )), ([Net Profit]2667 - 100) * 0.4) I would greatly appreciate your help. Thank you.
-
Double Notifications
I have a work flow set up for vacation requests at our company. This is what happens: Enter information on a form Form is sent to relevant project Manager for approval If approved it is copied to another sheet to populate a calendar view on our dashboards, if it is denied it kicks an email back to the submitter. I only…
