Subtotaling minus negative numbers and posative numbers
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 |
Subtotaling minus negative numbers and posative numbers
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 "lehigh46" wrote: 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 |
Subtotaling minus negative numbers and posative numbers
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 "lehigh46" wrote: 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 |
Subtotaling minus negative numbers and posative numbers
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(a20,a2,"") And use =subtotal() against that column. And add a second column with a similar formula for the negative values. lehigh46 wrote: 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 -- Dave Peterson |
All times are GMT +1. The time now is 09:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com