How to add only the adding filtered result using SUM function?

  • Thread starter Thread starter Kelie
  • Start date Start date
K

Kelie

Hello,

I have a column with some numbers and they're added up at the bottom
of the column with the SUM function. Now when I use a filter on
another column, some rows become hidden, but the sum does not change.
In this case, I'd like to just add those visible rows.

Thanks for your help!
 
use SUBTOTAL function
you might read about it in Help
Click to expand...

Jarek,

Thank you. That almost did exactly what I want. I should have
mentioned there are some negative numbers that I don't want to
include. So I was using the SUMIF function like this: SUMIF((E2:E22),
">0"). I guess SUBTOTALIF is not a valid function. Is it still
possible?
 
don't know if its doable with SUBTOTAL

you might use however this function

Function sum_plus(rng As Range) As Double
Dim cell As Range

For Each cell In rng.Cells
If Not cell.Rows.EntireRow.Hidden And cell.Value > 0 Then
sum_plus = sum_plus + cell.Value
End If
Next cell

End Function

press ALT+F11 to get to VBA window, then Insert->Module and paste this
code there

go back to your worksheet and insert
=sum_plus(your_range)
to calculate

pls click YES if it helped
 
Hi,

Assume the range is in F9:F13 (excluding the header row). Try this formula

=SUMPRODUCT(SUBTOTAL(9,OFFSET(F9:F13,ROW(F9:F13)-ROW(F9),,1))*(F9:F13>0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top