Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: conditional sum ifs - help please

  1. #1

    conditional sum ifs - help please



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have my data arranged as:
    Name1, Name2, TotalCount (please see attached file). I need to populate Columns D thru G with following logic:


    ColumD (Name1>100&Name2<25): if sum of totalcount over Name1> 100 and sum of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumD.


    ColumE (Name1<100&Name2<25): if sum of totalcount over Name1< 100 and sum of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumE.
    Note: This is the same as ColumnD with < 100


    ColumF (Name1>100&Name2<25): if sum of totalcount over Name1> 100 and min of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumF.
    good example are X7 records. Where sum of totalcoutn > 100 and only first record where Name2=Y15 and totalcount=6 takes the value of 1


    ColumG (Name1>100&Name2<25): if sum of totalcount over Name1< 100 and min of totalcount over name2 < 25, then 1 otherwise 0. Desired outcome in ColumG.
    Note: This is the same as ColumnF with < 100


    good example are X4 records where sum of totalcount< 100 but only the second record where Name2=Y6 and totalcount=7 take the value of 1


    Hope this is clear as to what I need. I believe the first two columns D& E can be accomplished by:
    =AND(C2 < 25, SUMIF($A$2:$A$19, A2, $C$2:$C$19) > 100)+0
    =AND(C2 < 25, SUMIF($A$2:$A$19, A2, $C$2:$C$19) < 100)+0
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    Try these formula in D2:G2, respectively:

    =AND(SUMIF($B$2:$B$19,$B2,$C$2:$C$19) < 25, SUMIF($A$2:$A$19, $A2, $C$2:$C$19) > 100)+0

    =AND(SUMIF($B$2:$B$19,$B2,$C$2:$C$19) < 25, SUMIF($A$2:$A$19, $A2, $C$2:$C$19) < 100)+0

    =AND(MIN(IF($B$2:$B$19=$B2,$C$2:$C$19)) < 25, SUMIF($A$2:$A$19, $A2, $C$2:$C$19) > 100)+0

    =AND(MIN(IF($B$2:$B$19=$B2,$C$2:$C$19)) < 25, SUMIF($A$2:$A$19, $A2, $C$2:$C$19) > 100)+0

    note: the last 2 are array formulas and need to be confirmed with cTRL+SHIFT+ENTER not just ENTER... then copy each formula down.

    Now, I am not sure why for the column F, you say that only the first X7 item should return a 1? Why not the second X7 item too? since there is only one Y16 and it is less than 25


  3. #3
    Thanks for the great formulas. The last two are not there yet and it's related to your question. I didn't demonstrate here but my actual data rarely contains common Name2 within Name1 like
    X7, Y14
    X7, Y15
    X7, Y15

    in this case your formula may work..I have yet to test it. For the original scenario, I need ONLY the smallest number to take value of 1. Thus, only X, Y15, 6 should take the value of 1 and the rest should be 0. Another word, I need the first occurrence of the smallest numbers take the value of 1. Is this clear?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    Then perhaps, the F2 formula should be:

    =AND(MIN(IF($A$2:$A$19=$A2,$C$2:$C$19)) < 25, $C2=MIN(IF($A$2:$A$19=$A2,$C$2:$C$19)),SUMIF($A$2:$A$19, $A2, $C$2:$C$19) > 100)+0

    and G2:

    =AND(MIN(IF($A$2:$A$19=$A2,$C$2:$C$19)) < 25, $C2=MIN(IF($A$2:$A$19=$A2,$C$2:$C$19)),SUMIF($A$2:$A$19, $A2, $C$2:$C$19) < 100)+0

    each confirmed with CTRL+SHIFT+ENTER and copied down


  5. #5
    Thanks a million...worked like a charm. Any special training to get good at formulas?

  6. #6
    I have one more variation to this...since we are creating this database that is regulatory being updated, I just been asked to add another filed based on this logic:
    X7, Y14, 20
    X7, Y15, 30
    X8, Y15, 60 1
    X9, Y15, 20
    if sum of Column C over column B > 100, then MAX of Column C = 1 otherwise 0. In this case, since sum of Y15 = 110, then the max which 60 take the value of 1 otherwise 0

    Thank You,

    Helal

  7. #7
    following your logic, I tried
    =AND($C2=MAX(IF($B$2:$B$4,$B2,$C$2:$C$4)),SUMIF($B$2:$B$4,$B2,$C$2:$C$4)>100)+0
    with CTRL+SHIFT+ENTER
    but did't work!

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    The comparison syntax in the MAX(IF()) formulation uses = to compare, whereas SUMIF uses , to compare...

    Try:

    =AND($C2=MAX(IF($B$2:$B$4=$B2,$C$2:$C$4)),SUMIF($B$2:$B$4,$B2,$C$2:$C$4)>100)+0


  9. #9
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,317
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Quote Originally Posted by Helal View Post
    Thanks a million...worked like a charm. Any special training to get good at formulas?
    Yes, read all posts by NBVC on the forums he is member of .

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by Pecoflyer View Post
    Yes, read all posts by NBVC on the forums he is member of .
    LOL



Page 1 of 2 1 2 LastLast

Posting Permissions

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