+ Reply to Thread
Results 1 to 11 of 11

Sum with multiple conditions

  1. #1
    Registered User
    Join Date
    09-01-2016
    Location
    sWITZERLAND
    MS-Off Ver
    2013
    Posts
    14

    Sum with multiple conditions

    Hi All

    I need to sum data based on multiple conditions. Depends on chosen customer from dropdown list:
    1. if specific site(column E), marked as base (column F) and marked as specifid customer (column H-L)
    Then sum and show in cell next to the site name

    Please see example attached

    Thank you for your help
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Sum with multiple conditions

    TRy

    =SUMPRODUCT(($D$3:$D$6)*($E$3:$E$6=$D22)*(INDEX($H$3:$L$6,,MATCH(E$21,$H$2:$L$2,0))))

    Your customer matrix has a one (1) in each cell rather than "AAAAAA" etc
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Sum with multiple conditions

    In your example in the bottom table you have values for Brugge and Derby however you'd suggest one of your criteria is that it's marked as Base in column F which neither of these are.

    Also it's not clear to me in your customer table at the top right handside how this is being used. Are you wanting to check is AAA,BBB etc if contained in any of those columns or will they always be in and order?

  4. #4
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Sum with multiple conditions

    Looks like John understands. Thanks John

  5. #5
    Registered User
    Join Date
    09-01-2016
    Location
    sWITZERLAND
    MS-Off Ver
    2013
    Posts
    14

    Re: Sum with multiple conditions

    Hi Yes sorry I made a mistake tables hould be showing only positions for the base I have updated the excel example

    I tried the solution from John. Somehow it comes back with value error and yes needs one more condition of the Base and option
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Sum with multiple conditions

    Perhaps if you followed my post ..

    Your customer matrix has a one (1) in each cell rather than "AAAAAA" etc
    and looked at the example solution I posted ....

  7. #7
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Sum with multiple conditions

    KaBom you can try the below which is an addition to Johns formula to crack the IF part. You will however need to change the value in the Customer Table from H3:L6 to the number 1 instead of AAAAAA etc like John eluded to earlier.

    Place this into E22 and drag across and down and remember to change the AAAAAA etc to number 1 in the table but leave the headers in row2

    =IF(VLOOKUP($D22,$E$3:$F$6,2,0)="Base",SUMPRODUCT(($D$3:$D$6)*($E$3:$E$6=$D22)*(INDEX($H$3:$L$6,,MATCH(E$21,$H$2:$L$2,0)))),0)

  8. #8
    Registered User
    Join Date
    09-01-2016
    Location
    sWITZERLAND
    MS-Off Ver
    2013
    Posts
    14

    Re: Sum with multiple conditions

    Hi Yes I have checnged it all to "1" . I will figure this one out However I dont know how to add another condition. the one connected to the base/option.
    Thank you for all your time

  9. #9
    Registered User
    Join Date
    09-01-2016
    Location
    sWITZERLAND
    MS-Off Ver
    2013
    Posts
    14

    Re: Sum with multiple conditions

    Will try it now thank you both
    you are great help

  10. #10
    Registered User
    Join Date
    09-01-2016
    Location
    sWITZERLAND
    MS-Off Ver
    2013
    Posts
    14

    Re: Sum with multiple conditions

    Hi

    I Have implemented the lookup solutioon to create formula dividing HRS from column D according to site , base or different options, and different customer
    =IF(VLOOKUP($D18;$E$3:$F$6;2;0)=H12;SUMPRODUCT(($D$3:$D$6)*($E$3:$E$6=$D18)*(INDEX($H$3:$J$6;;MATCH(E$17;$H$2:$J$2;0))));"WHY")

    Above formula gives 2 errors .
    1. If (first cell) F3 is not BASE then it doesn't sumup
    2. it sums up all from chosen site not looking if it is base or option

    Thank you for your help
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Sum with multiple conditions

    Try

    =SUMPRODUCT(($D$3:$D$6)*($F$3:$F$6=$D$16)*($E$3:$E$6=$D18)*(INDEX($H$3:$L$6,,MATCH(E$17,$H$2:$L$2,0))))

    Adjust ranges for each table

    It would be easier if you made the "options" a drop down then a single formula would do, selecting "Base", "OptXX", "OPTyy" from the drop down
    Attached Files Attached Files
    Last edited by JohnTopley; 12-06-2016 at 01:03 PM.

+ 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. Replies: 2
    Last Post: 01-14-2016, 03:53 PM
  2. [SOLVED] Macro for data present in multiple rows and columns following multiple conditions
    By macrolearnerkk in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-07-2015, 11:20 AM
  3. [SOLVED] Vlookup - Country conditions (Multiple conditions)
    By dluhut in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2013, 11:42 AM
  4. Replies: 0
    Last Post: 08-14-2012, 01:18 PM
  5. Insert multiple values from seperate table based on multiple conditions
    By drakesong in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-28-2011, 06:04 PM
  6. Replies: 1
    Last Post: 12-05-2010, 04:39 AM
  7. [SOLVED] How to multiple conditions to validate more than 2 conditions to .
    By Bhuvana Govind in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2005, 04:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1