Subtotaling minus negative numbers and posative numbers

L

lehigh46

Hi All,

I have a column with a mix of positive and negative numbers.
What Subtotal formulas can I use to sum only the positive numbers and
a second formula below the first one to show negative numbers.
This column will be auto filtered so I need a "Subtotal" function.

Thanks for your help

Tom
 
B

bj

I don't believe you will be able to do it with just a single subtotal
several ways to do it
=sum(if(and(d:d<0,autofilter criteria),D:D)) entered as array
control-shift-enter
might work
two helper columns with
=max(d1,0)
=min(D1,0)
and subtotal these helper columns should work
 
M

Mike H

With a macro

Sub sonic()
Set myrange = Range("A2:A9")
myrange.SpecialCells(xlVisible).Select
For Each c In Selection
If c.Value < 0 Then
negtot = negtot + c.Value
Else
postot = postot + c.Value
End If
Next
MsgBox (negtot)
MsgBox (postot)
End Sub


Note if you go this route you could wrte the results to a cell on your sheet
and you should include some error checks for text values.

Mike
 
D

Dave Peterson

I bet you get a better answer than this, but you could always insert an extra
column in your filtered range and use a formula like:

=if(a2>0,a2,"")

And use =subtotal() against that column.

And add a second column with a similar formula for the negative values.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top