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

Topic

New Reply

I’m back again…

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:

• find the rows in column B that apply to a particular term
• sum values that are positive and put them in the cell for Long in that term…
• 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?

Reply | Quote

Replies

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$170),$E$5:$E$17))
In K5:
=SUM(IF(($B$5:$B$17>=$H5)*($B$5:$B$17<$I5)*($E$5:$E$17=$H12)*($E$5:$E$17>0),$E$5:$E$17))
In K12:
=SUM(IF(($B$5:$B$17>=$H12)*($E$5:$E$170, but in row12 they can NOT be <0!

Steve

Reply | Quote

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

Ummm… at the risk of completely embarrassing myself with a dumb question…. (Just kidding… This is how I learn… I MUST know why… )
what’s with the asterisks between the conditions?

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

Reply | Quote

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$170),$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$170),$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%5B/url%5D

Steve

Reply | Quote

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!
Thanks again!

Reply | Quote

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

Reply | Quote