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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Tom Morales

Board Regular
Joined
Feb 16, 2002
Messages
91
Check out "SUMIF"
Tom
_________________
This message was edited by Tom Morales on 2002-08-31 07:35
 

Riaang

Board Regular
Joined
Aug 29, 2002
Messages
146
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

Board Regular
Joined
Feb 16, 2002
Messages
91
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

Board Regular
Joined
Aug 29, 2002
Messages
146

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

Board Regular
Joined
Aug 29, 2002
Messages
146
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Aug 29, 2002
Messages
146
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.
 

Forum statistics

Threads
1,144,292
Messages
5,723,537
Members
422,502
Latest member
barakgahtan

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
Top