Hi,
I need a bit of help please and I will try my best to make it as simple as possible!
What I am trying to achieve is add some extra functions to the end of an existing SUMIF function in column D based on conditions being met from 2 different columns (Column C and Column H) which I have highlighted in red in the image below and also on my attached spreadsheet.
As shown in the below image, currently in column D I have the current formula:
SUMIF($I:$I,I3,$C:$C)
Capture6.PNG
This formula is currently using the Total Delay Minutes (Column C), and getting the sum of all the values from this column where Event ID (Column I) is XXX.
However, I also want to add the following 2 conditions to the end of the existing SUMIF formula and if both conditions are met to do an additional calculation. The 2 conditions are:
1. The Delay Minutes column (Column C) is greater than or equal to 60
2. And also the Status Code column (Column H) contains at least 1 “AOG” status code for a specific Event ID.
If these 2 conditions is TRUE and is met, then at the end of my existing formula SUMIF($I:$I,I3,$C:$C) I want to do an additional calculation which is:
I want to add “360” and multiple this number by the value in the “Number of Nights Active?” column (Column G).
As shown in my image, for your reference, I have manually entered the values I expect in the Total Delay Minutes for Event ID column (Column D). Prior to my manual entries I used the formula SUMIF($I:$I,I3,$C:$C) as I also need to obtain the total delay minutes from column C for a given Event ID (Column I). However, this formula alone does not take into account column C being >= 60 minutes and at least 1 of the status code in column H being “AOG” for a specific Event ID.
As shown in cells D3 – D9 (Event ID 211966), the function I want to add (+360*value in column H) is irrelevant as only 1 of my conditions is met (“AOG” status code is met but delay minutes >= 60 is not met). So the calculation of +360*value in column H does not apply.
As shown in cells D16 – D19 (211825), both my conditions are met so my additional calculation applies, which should give a figure of 3960. The current SUMIF function I am using returns 3240.
Could someone please help me with this? I am willing to clarify anything that is unclear as I am sure what I have just said may not be easy to understand! Thanks
Bookmarks