Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
col of minus numbers changed to plus numbers | Excel Worksheet Functions | |||
How do you put negative/minus numbers in brackets | Excel Discussion (Misc queries) | |||
How do I change minus numbers to + numbers in a col in Excel? | Charts and Charting in Excel | |||
ADD NEGATIVE NOT POSATIVE | Charts and Charting in Excel | |||
convert negative numbers to positive numbers and vice versa | Excel Worksheet Functions |