Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show value based on the sum of multile conditions (Excel 2003)

    I need to lookup on 3 criteria and sum the corrsponding group then a "Y" or "N' be input in column C
    For example, Products in Column A such as XYZ, BBK....etc
    Names in Column B such as ABC 01, ABCD 001, XY 0001 and so on....the names can be varies in length, but we
    can identify by the group name, like ABC xxx, XY xxx ...etc. there is always a space after the group name

    In Column D, I have Code like "US", "CA", "SG"...etc for the corresponding rows and in column B, I have
    amount for related to each names.

    Let say I need to first group "Product" in column A, then "Names" in column B, and then "Code" in column D,
    then sum the "Amount" in column B for the Group under the same Product. If the total sum of the amount is
    less than 100, put a "N" in Column C of the corresponding rows, otherwise put a "Y" if the amount is greater
    than 100.

    TIA

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show value based on the sum of multile conditions (Excel 2003)

    Hi All

    I have attached a sample to show the result.

    Thanks
    regards, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show value based on the sum of multile conditi

    Cell E2, entered the formula and copied down

    =IF(SUMIF(A:A,A2,C:C)>100,"Y","N")

    Regards
    Bosco

  4. #4
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show value based on the sum of multile conditi

    Hi Bosco

    The formula is looking at column A and return the result in column E.
    However, I need it to look at Col A, Col B and Col D with matching criteria, then return the result in Col E

    I have derive a formula but it look at only Col B, sum the total in Col C and return the result in Col E
    How do I include an additional condition for Col A and Col D

    =IF(SUMIF(B:B,LEFT(B2,FIND(" ",C2&" ")-1) & "*",C:C)<100,"N","Y")

    thanks

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Show value based on the sum of multile conditi

    Something like this perhaps
    =IF(SUMPRODUCT(($A$2:$A$100=A2)*(LEFT($B$2:$B$100, FIND(" ",$B$2:$B$100&" "))=LEFT(B2,FIND(" ",B2&" ")))*($C$2:$C$100)*($D$2:$D$100=D2))>100,"Y"," N")

    Note: Adjust the ranges as desired, you can not use the whole column (you will get a #NUM error). You should try to minimize the number of rows you do check as these types of arrays make the spreadsheet sluggish due to the vast number of calcuations each individual formula does.

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show value based on the sum of multile conditi

    Or……

    =IF(SUMPRODUCT(($A$2:$A$100=A2)*ISNUMBER(FIND(LEFT (B2,FIND(" ",B2&" ")),B$2:B$100))*($C$2:$C$100)*($D$2:$D$100=D2))>10 0,"Y","N")

    Regards
    Bosco

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Show value based on the sum of multile conditi

    This won't neccessarily yield the same results as what was asked for. for example, if B2 has "XL xxx" and there are items in B like "EXLAX xxxx" the "XL" will be found in the EXLAX but it will not match all of the beginning part.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show value based on the sum of multile conditi

    Hi Steve

    Great and thanks. I have approximately close to 1200 rows and I think it fine using this formula.
    Appreciate if you can tell me how does this formula works?

    TIA

    cheers,
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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