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

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!

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

use SUBTOTAL function
you might read about it in Help


On 27 Maj, 08:38, Kelie wrote:
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!

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

On May 26, 8:49*pm, Jarek Kujawa wrote:
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?

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

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

On 27 Maj, 09:40, Kelie wrote:
On May 26, 8:49*pm, Jarek Kujawa wrote:

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?

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

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:F130))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Kelie" wrote in message
...
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!

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

Jarek, Ashish:

Both methods work well. Thanks for your help!

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

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Kelie" wrote in message
...
Jarek, Ashish:

Both methods work well. Thanks for your help!

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

thanks for the feedback

pls click YES if it helped

On 29 Maj, 10:03, Kelie wrote:
Jarek, Ashish:

Both methods work well. Thanks for your help!