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

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•