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

[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!

 

[Jarek Kujawa]

use SUBTOTAL function
you might read about it in Help

 

[Kelie]

use SUBTOTAL function
you might read about it in Help

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?

 

[Jarek Kujawa]

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

 

[Ashish Mathur]

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

 

[Kelie]

Jarek, Ashish:

Both methods work well. Thanks for your help!

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jarek Kujawa]

thanks for the feedback

pls click YES if it helped