Results 1 to 6 of 6

20030624, 15:24 #1
 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 SR1)
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?

20030624, 15:51 #2
 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 SR1)
You need an ARRAY formula (confirm with ctrlshiftenter) [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

20030624, 16:46 #3
 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 SR1)
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

20030624, 17:29 #4
 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 SR1)
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>

20030624, 17:56 #5
 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 SR1)
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

20030624, 18:13 #6
 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 SR1)
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>