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

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

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?

2. ryandric,

Welcome to the Lounge as a new poster!

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

Setup:
STBase.PNG

Select Groups A&C:
STGroupFilter.PNG

Set Number Filter:
STGTZero.PNG

Results:
STResults.PNG

HTH

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

4. ryandric,

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

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

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

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

8. Zeddy,

This VBA module works perfectly.
Thank you.

ryandric