Sum visible (or filtered) range in an Excel Spreadsheet

Question

In Excel, I need to filter and display the COUNTIF & SUM of both the global range and the visible (or filtered) range.

I can already display the COUNTIF & SUM of the global range with the following code.

AtmCount = Application.WorksheetFunction.CountIf(Range("X3:X4533"), ">0")
AtmSum = Application.WorksheetFunction.Sum(Range("X3:X4533"))

I can also get the COUNT of the visible (or filtered) range as follows:

AtmCurrentCount = Range("X3:X4533").SpecialCells(xlCellTypeVisible).Count

However, this still leaves the SUM of visible (or filtered) range outstanding.

AtmCurrentSum = ???

I really stuck. Please, can somebody help me?

This may not be the ideal solution but if you copy the used range onto a new sheet it will only copy the filtered data allowing you to do whatever calculations you need more easily — Ripster, Jul 24 '13 at 13:25

score 7 · Accepted Answer · edited May 23 '17 at 12:25

This will do what you want. Set visibleTotal to the appropriate data type for the total, and change the ws and rng objects to match what you have in your workbook.

Sub SumVisible()
    Dim ws As Worksheet
    Dim rng As Range
    Dim visibleTotal As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("B1:B7")

    ws.AutoFilterMode = False
    rng.AutoFilter field:=1, Criteria1:=5

    visibleTotal = Application.WorksheetFunction.Sum(rng.SpecialCells(xlCellTypeVisible))
    ' print to the immediate window
    Debug.Print visibleTotal
End Sub

In case you only want to sum part of the filtered range (e.g. you filter on column A but want the sum of column B), see this question and answer: Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table.

score 3 · Answer 2 · answered Feb 19 '19 at 23:38

If one need to COUNT the number of visible items in a filtered list, then use the SUBTOTAL function, which automatically ignores rows that are hidden by a filter.

The SUBTOTAL function can perform calculations like COUNT, SUM, MAX, MIN, AVERAGE, PRODUCT and many more (See the table below). It automatically ignores items that are not visible in a filtered list or table. This makes it ideal for showing how many items are visible in a list, the subtotal of visible rows, etc. It also provide control rows hided manually manually.

The solution to your question would be to count the number of non-blank rows visible in Column A and Column B when a filter is active, use:

AtmCurrentSum = Application.WorksheetFunction.Subtotal(109, Range("$X$3:$X$4533"))

Points to remember when you apply SUBTOTAL formula:

When function_num (First argument) is between 1-11, SUBTOTAL includes values that are hidden manually but ignore hidden by filter.
When function_num is between 101-111, SUBTOTAL excludes all kind of hidden values.
In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num.
SUBTOTAL ignores other subtotals that exist in references are ignored to prevent double-counting
SUBTOTAL only work with vertical data values arranged vertically.
In Horizontal Hidden Columns, values are always included and never ignored.

I want to utilize the 109 feature in the Data tab/Subtotal in VBA. Like: `Range(A1).Subtotal Groupby:=1, Function:=xlSum, TotalList:=Array(2)`. Do you know the corresponding argument for `109` to go into `Function:=`? — JackeyOL, Aug 10 '21 at 17:24
I believe, it will remain xlSum, however, to filter on visible cells, you need to use Range("A:A").SpecialCells(xlcelltypevisible).SubTotal GroupBY:=1, Function:=xlSum, TotalList:=Array(2) — jainashish, Aug 11 '21 at 10:45
@jainashish Yes, it works fine. I put the VBA line you wrote in a function and call that function in a cell using formula: =AtmCurrentSum() .The only issue I experience is, when I click filter and change the visible cells, the function is not called and so the sum is not RE-calculated for new visible cells. I have to go into formula bar and press enter for re-calculation, the bad way! I appreciate your help. — Fighter Jet, Sep 05 '22 at 06:41

JosieP · Answer 3 · 2013-07-26T06:48:28.460

1

if you only want sum rather than sumif

AtmCurrentSum = application.worksheetfunction.subtotal(9, Range("X3:X4533"))

edited Jul 26 '13 at 06:48

answered Jul 24 '13 at 13:56

JosieP

3,360
1
13
16

I've added the variable-thought it was self-explanatory ;-) – JosieP Jul 24 '13 at 15:07
Not the variable ;) When I added your function `WorksheetFunction... it gives an error on the code – CustomX Jul 25 '13 at 07:01
I've added the `application.` part though it isn't usually necessary – JosieP Jul 26 '13 at 06:48

score 0 · Answer 4 · answered Sep 21 '18 at 01:04

The give function Sub SumVisible() did not worked for me instead i used Subtotal function.Caution use function number 109,102,104,105 to ignores hidden values. i.e. to calculates only on visible cells.

"https://support.office.com/en-us/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939"

aSum = WorksheetFunction.Subtotal(109, CalcRange) 
aCount = Application.WorksheetFunction.Subtotal(102, CalcRange)
aMax = Application.WorksheetFunction.Subtotal(104, CalcRange)
aMin = Application.WorksheetFunction.Subtotal(105, CalcRange)

Sudhanshu Soni · Answer 5 · 2020-01-16T14:01:17.730

0

If you are using Excel 2016, this will work

Sum = Application.WorksheetFunction.Subtotal(9, Range("$D2:D" & Rows(Rows.Count).End(xlUp).Row))

9 specifies that you want to sum of visible rows My data in which I had to find sum was in D column Hope this will help :)

edited Jan 16 '20 at 14:01

answered Nov 23 '19 at 12:15

Sudhanshu Soni

1
2

score -1 · Answer 6 · answered Dec 22 '17 at 00:33

I just came across this site. https://www.techrepublic.com/blog/microsoft-office/how-to-sum-values-in-an-excel-filtered-list/

The basic formula is x = Application.WorksheetFunction.Subtotal('code number', 'range') The formula you want is x = Application.WorksheetFunction.Subtotal(109, range(x, y))

Sum visible (or filtered) range in an Excel Spreadsheet

6 Answers6

Linked

Related