+ Reply to Thread
Results 1 to 12 of 12

Additional calculation providing both conditions are met

  1. #1
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    27

    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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    27

    Re: Additional calculation providing both conditions are met

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

  3. #3
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    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
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,059

    Re: Additional calculation providing both conditions are met

    Did you try SUMIFS function?

  5. #5
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    27
    No. What could I try?
    Last edited by AliGW; 01-18-2020 at 02:55 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    27
    Quote Originally Posted by Ace_XL View Post
    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!

  7. #7
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    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

  8. #8
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    27

    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?
    Last edited by AliGW; 01-18-2020 at 02:55 AM. Reason: Please don't quote unnecessarily!

  9. #9
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    27

    Re: Additional calculation providing both conditions are met

    Quote Originally Posted by Ace_XL View Post
    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!

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,654

    Re: Additional calculation providing both conditions are met

    If I understand correctly then Ace_XL's formula could be modified to read:
    Formula: copy to clipboard
    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: copy to clipboard
    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  11. #11
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    27

    Re: Additional calculation providing both conditions are met

    Quote Originally Posted by JeteMc View Post
    If I understand correctly then Ace_XL's formula could be modified to read:
    Formula: copy to clipboard
    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: copy to clipboard
    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!

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,654

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] IFERROR is providing correct answer but not providing a blank Cell when it should
    By marshallmr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-05-2016, 01:46 PM
  2. vlook up with additional conditions - please help!
    By hayley b in forum Excel General
    Replies: 8
    Last Post: 08-09-2012, 08:24 AM
  3. additional month calculation
    By BorisS in forum Excel General
    Replies: 6
    Last Post: 12-13-2005, 02:30 AM
  4. [SOLVED] Additional Conditions for Conditional Formatting
    By David McRitchie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2005, 06:05 PM
  5. Additional Conditions for Conditional Formatting
    By David McRitchie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 11:05 AM
  6. [SOLVED] Additional Conditions for Conditional Formatting
    By David McRitchie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  7. Additional Conditions for Conditional Formatting
    By eric beck in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts