Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to add only the adding filtered result using SUM function?
Jarek, Ashish:
Both methods work well. Thanks for your help! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a sequence to filtered data | Excel Discussion (Misc queries) | |||
I want to add up the result of a filtered list | Excel Worksheet Functions | |||
HOW TO GET A DESIRED 'SUMIF' RESULT FROM A FILTERED DATA | Excel Worksheet Functions | |||
Adding only the filtered results | Excel Worksheet Functions | |||
Adding text to the result of a formula? | Excel Worksheet Functions |