# Additional calculation providing both conditions are met

1. ## Additional calculation providing both conditions are met

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   Register To Reply

2. ## Re: Additional calculation providing both conditions are met

If anyone could help in regards to this I would really appreciate it   Register To Reply

3. ## Re: Additional calculation providing both conditions are met

Try in D3

=SUMIFS(\$C:\$C,\$I:\$I,I3)+(360*G3)*(AND(C3>=60,COUNTIFS(I:I,I3,H:H,"AOG")))

Copy down  Register To Reply

4. ## Re: Additional calculation providing both conditions are met

Did you try SUMIFS function?  Register To Reply

5. No. What could I try?   Register To Reply

6. Originally Posted by Ace_XL Try in D3

=SUMIFS(\$C:\$C,\$I:\$I,I3)+(360*G3)*(AND(C3>=60,COUNTIFS(I:I,I3,H:H,"AOG")))

Copy down
Hi, thanks for this! But unfortunately it is not returning the correct values in column D. For example, for cells D16 - D19, all the cells should return the value 2886, however only cells D16 and D19 is returning this. The other 2 cells D17 and D18 are returning 2166.

To clarify what I want and apologies as I believe I didnt explain it clearly. I want the calculation of 360 * value in column G to take place as long as at least one cell for a specific Event ID (Column I) contains a delay of 60 or more in column C.

So for example, even if cell C17 contains 0 which does not meet the criteria of being greater than or equal to 60. However, both cells C16 and C19 (which is the same Event ID) contain a value equal to or greater than 60, so I want the formula to ensure its still calculating 360 * value in column G, as at least 1 value for a specific Event ID (Column D) is greater than or equal to 60.

Would it be possible for you to amend the formula you initially provided to give the results I am looking for please? Thanks!   Register To Reply

7. ## Re: Additional calculation providing both conditions are met

I don't get the values you're suggesting in D16-D19 with the formula I suggested in the earlier post (I get 3960 and 3240). But based on my understanding of what you've mentioned try in D3

=SUMIFS(\$C:\$C,\$I:\$I,I3)+(360*G3)*(AND(COUNTIFS(I:I,I3,C:C,">="&60),COUNTIFS(I:I,I3,H:H,"AOG")))

Copy down  Register To Reply

8. ## Re: Additional calculation providing both conditions are met

Thank you this has worked! However, are you kindly able to briefly explain what your formula is exactly doing please?   Register To Reply

9. ## Re: Additional calculation providing both conditions are met Originally Posted by Ace_XL I don't get the values you're suggesting in D16-D19 with the formula I suggested in the earlier post (I get 3960 and 3240). But based on my understanding of what you've mentioned try in D3

=SUMIFS(\$C:\$C,\$I:\$I,I3)+(360*G3)*(AND(COUNTIFS(I:I,I3,C:C,">="&60),COUNTIFS(I:I,I3,H:H,"AOG")))

Copy down
Hi, are you or anybody else able to help me further please? I want to make some changes, where initially I want to count if column H contains just "AOG" for a specific Event ID (Column I).

How can I do it so it counts if it column H meets any of the following: AOG, 40, 50 or 61 for a specific Event ID (Column I) and not just "AOG"?

Would appreciate it greatly if someone could help me please!   Register To Reply

10. ## Re: Additional calculation providing both conditions are met

If I understand correctly then Ace_XL's formula could be modified to read:
Formula:  `Please Login or Register  to view this content.`

OR put the status codes you want to include in a range such as N3:N6 and modify the formula to read:
Formula:  `Please Login or Register  to view this content.`

The second option would allow you to quickly change which status codes to include without having to change the formula.
Let us know if you have any questions.  Register To Reply

11. ## Re: Additional calculation providing both conditions are met Originally Posted by JeteMc If I understand correctly then Ace_XL's formula could be modified to read:
Formula:  `Please Login or Register  to view this content.`

OR put the status codes you want to include in a range such as N3:N6 and modify the formula to read:
Formula:  `Please Login or Register  to view this content.`

The second option would allow you to quickly change which status codes to include without having to change the formula.
Let us know if you have any questions.
Thank you so much for this!   Register To Reply

12. ## Re: Additional calculation providing both conditions are met

You're Welcome. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 