Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum positive values if 2 conditions are true... (2000 SR-1)

    I'm back again... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I have created and attached a sample of what I'm working with...
    The left side of the worksheet will contain a longgg list of products, maturity dates and their details...
    The right side is going to be a summary of the companies' Long and Short positions, split into different Terms...

    I worked out the formulas to get the right dates for each term...
    Now what I need is a formula that will:
    <UL><LI>find the rows in column B that apply to a particular term
    <LI>sum values that are positive and put them in the cell for Long in that term...
    <LI>sum values that are negative and put them in Short for that term.... [/list]Is this possible?
    I read that SumIf only works with one condition... I tried a formula with some nested If's, but it didn't work...
    Any ideas?

  2. #2
    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: Sum positive values if 2 conditions are true... (2000 SR-1)

    You need an ARRAY formula (confirm with ctrl-shift-enter) [change the range sizes as appropriate)
    In J5:
    =SUM(IF(($B$5:$B$17>=$H5)*($B$5:$B$17<$I5)*($E$5:$ E$17>0),$E$5:$E$17))
    In K5:
    =SUM(IF(($B$5:$B$17>=$H5)*($B$5:$B$17<$I5)*($E$5:$ E$17<0),$E$5:$E$17))

    Copy these from 5 to rows 6 - 11
    In J12:
    =SUM(IF(($B$5:$B$17>=$H12)*($E$5:$E$17>0),$E$5:$E$ 17))
    In K12:
    =SUM(IF(($B$5:$B$17>=$H12)*($E$5:$E$17<0),$E$5:$E$ 17))
    The 12th row is different in that you don't have a MAX date. You could use the same formula and add a HIGH value in I12 if desired. The lack of MIN in H5 does NOT matter since it is considered 0, and the values are >0, but in row12 they can NOT be <0!

    Steve

  3. #3
    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: Sum positive values if 2 conditions are true... (2000 SR-1)

    Option Number 2:
    Make an Intermediate table of results (3 columns, probably in second worksheet, could be hidden): Terms, Long, and Short
    Terms is:
    =+INDEX($L$5:$L$12,MATCH(B5,$M$5:$M$12))
    Long is:
    =max(e5,0)
    Short is:
    =min(e5,0)
    Copy it down as many rows as the source.

    Create a PIVOT table from these 3 columns
    Row = terms
    Data = sum of Long, sum of short
    When finished 3 columns, terms, data (short/long), total. drag the data header over to total to make it columns and you have your table.

    Steve

    Copy these

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum positive values if 2 conditions are true... (2000 SR-1)

    Thanks a million Steve! Option 1 worked perfectly on the first try...

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> Ummm... at the risk of completely embarrassing myself with a dumb question.... (Just kidding... This is how I learn... I MUST know why... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>)
    what's with the asterisks between the conditions?

    * = multiplication operator OR wildcard operator to me... Does it mean AND in this case?

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  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: Sum positive values if 2 conditions are true... (2000 SR-1)

    Yes. The asterisk is for multiply which does the AND. (FYI, plus (+) does the OR)
    True = 1, False = 0,
    True * True *True = 1*1*1 = 1 = True
    Any FALSE and the whole condition is FALSE (which is why it is an AND)
    With an OR (adding them together) the ONLY to get a FALSE (=0) is for ALL to be false, if ANY are NOT false, then the value will <>0 so will be considered TRUE. (Excel is NOT strict that TRUE must = 1, FALSE = 0, if NOT false it is true. ANY number (in boolean logic) will be TRUE if not equal to zero.

    If all 3 conditions are true (for a row) you will take the value of the row in E
    So the ARRAY:
    =(IF(($B$5:$B$17>=$H5)*($B$5:$B$17<$I5)*($E$5:$E$1 7>0),$E$5:$E$17))
    will give a 18 row, 1 column array consisting of either (if ALL conditions are true) the VALUE in Col E or FALSE (I did NOT put a "if false clause")
    The SUM part:
    =SUM(IF(($B$5:$B$17>=$H5)*($B$5:$B$17<$I5)*($E$5:$ E$17>0),$E$5:$E$17))
    Sums that column so you get the values that meet all the criteria.

    Chip Pearson has a good overview of arrays on his website:
    http://www.cpearson.com/excel/array.htm

    Steve

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum positive values if 2 conditions are true... (2000 SR-1)

    Thanks Steve... I do know about logical operators and arrays... I just didn't know the * and + worked as AND and OR in Excel... COOL STUFF!
    I was actually trying to use AND in the formula and it wouldn't work, so I went on to trying nested IFs... This is useful knowledge! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Thanks again! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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