# Sum only negative or positive amounts

#### Riaang

##### Board Regular
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.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Check out "SUMIF"
Tom
_________________
This message was edited by Tom Morales on 2002-08-31 07:35

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

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

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.

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

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

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

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

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.

Replies
8
Views
440
Replies
11
Views
870
Replies
2
Views
609
Replies
0
Views
978
Replies
3
Views
577

1,217,256
Messages
6,135,499
Members
449,943
Latest member
thsix

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back