Subtotaling minus negative numbers and posative numbers

[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

 

[Guest]

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)) 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

 

[Guest]

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

 

[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.