Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2016
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

    Thank you for your help.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Mar 2016
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    ryandric,

    So is there a reason you can't filter Column C for numbers > 0?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    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. #6
    New Lounger
    Join Date
    Mar 2016
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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. #8
    New Lounger
    Join Date
    Mar 2016
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Zeddy,

    This VBA module works perfectly.
    Thank you.

    ryandric

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •