Using Subtotal to sum filtered column of numbers but exclude negative values

Topic

New Reply

I am trying to find out how to sum a column of filtered numbers but not include any negative values in the sum.
The SUBTOTAL function works on the filtering part but I can’t find a way to limit it to non-negative numbers.
Any suggestions?

Thank you for your help.

Reply | Quote

Replies

ryandric,

Welcome to the Lounge as a new poster! :cheers:

Are you using SubTotal on more than one column? If not just extend your filtering to filter the SubTotaled column to numbers > 0.

Setup:
43767-STBase

Select Groups A&C:
43768-STGroupFilter

Set Number Filter:
43769-STGTZero

Results:
43770-STResults

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thank you for your quick reply. I should have been clearer in my explanation.
I am filtering on columns A and B but Subtotaling column C. Columns A and B are text and column C has numbers in it.
The Subtotaled column does not have a filter.

Reply | Quote

ryandric,

So is there a reason you can’t filter Column C for numbers > 0? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

ryandric,

The following formula will subtotal positive numbers in a filtered list. It assumes you have a header in cell A1 and the data is in the range A2:A99. You can place the formula in A100

=SUMPRODUCT(–(A2:A99>0),SUBTOTAL(9,OFFSET(A2:A99,ROW(A2:A99)-ROW(A2),0,1)))

HTH,
Maud

Reply | Quote

Maud,

Thank you. This is what I was looking for. I am not able to filter column C because the negative values still need to be visible.

Reply | Quote

Hi ryandric

I like Maud’s formula method.

..another method is to use a custom function:

Code:

Function sumVisiblePositive(r As Range)

For Each cell In r.Cells
If cell.Height  0 Then
zValue = cell.Value
If IsNumeric(zValue) Then
If zValue > 0 Then
zTotal = zTotal + zValue
End If
End If
End If
Next

sumVisiblePositive = zTotal

End Function

You add this code to a standard vba module, and use it in the spreadsheet like any Excel formula, for example
=sumVisiblePositive(A2:A99)

zeddy

Reply | Quote

Zeddy,

This VBA module works perfectly.
Thank you.

ryandric

Reply | Quote