Sum only negative or positive amounts

[Riaang]

Hi, I have a spreadsheet of members accounts ageing detail for a company spliting them according to the differnt sections within the company. It gives the name, ref no, section code and then the ageing for 90days, 60days, 30days and current. What I want to do is add subtotals at each change in the section code and add totals to 90days, 60days, 30days and current. The catch is I only want it to add all the negative numbers per ageing group and then after extracting the totals it must do the same but with the positive numbers. eg if in the 90days column you have member1 with 300.00, member2 with -600.00 and member3 with 200.00 the answer I want in the 90days column must be -600.00 if summing the negatives and 500.00 when summing the positives.

 

[Tom Morales]

Check out "SUMIF"
Tom
_________________
This message was edited by Tom Morales on 2002-08-31 07:35

 

[Riaang]

Hi Tom, I am currently doing it with sumif but I want it to be done all at once like when you add subtotals.

 

[Tom Morales]

Riaang,
Maybe I'm missing something in your description.
Couldn't you have one row at the bottom totalling all the positives, and the next row below that totaling all the negatives? That would be simultaneous, no?
Tom

_________________
This message was edited by Tom Morales on 2002-08-31 10:10

 

[Riaang]

Hi Tom, the problem is that the file is for a specific company wich is grouped according to division codes. I want the subtotal at each change in the division codes.

 

[Brian from Maui]

This message was edited by Brian from Maui on 2002-09-02 00:01

 

[Aladin Akyurek]

On 2002-09-01 23:42, Riaang wrote:
Hi Tom, the problem is that the file is for a specific company wich is grouped according to division codes. I want the subtotal at each change in the division codes.

What is your current formula? Where do you have the division codes?
This message was edited by Aladin Akyurek on 2002-09-02 00:32

 

[Riaang]

Hi Aladin, The division code is in column L and I'm currently using a formula like this
=SUMIF(E4:E301,">0"). The amounts are lying in columns E to J.
This message was edited by Riaang on 2002-09-02 00:30

 

[Aladin Akyurek]

On 2002-09-02 00:30, Riaang wrote:
Hi Aladin, The division code is in column L and I'm currently using a formula like this
=SUMIF(E4:E301,">0"). The amounts are lying in columns E to J.
This message was edited by Riaang on 2002-09-02 00:30

=SUMPRODUCT((E4:E301>0)*(L4:L301="DivisonA"),E4:E301)

will give the sum of positive numbers related to DivisionA.

Change > to< for a sum of negative numbers.
This message was edited by Aladin Akyurek on 2002-09-11 04:41

 

[Riaang]

Hi is there anybody else that can help. I want this to be done by selecting the whole spreadsheet and it will give me the different totals after each code.