Sum only negative or positive amounts

Riaang

Board Regular
Joined
Aug 29, 2002
Messages
146
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
Hi Tom, I am currently doing it with sumif but I want it to be done all at once like when you add subtotals.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top