 # getting the sum of filtered data without the use of formulas

• Hi all,

I'm looking for a solution where I would use filters to get the data I need to count. I want to avoid using formulas in the workbook, but I need to create a report that would count lets say some cases that are open. Once I filter the sheet on criteria to see the results (I want to do this with vba), it should give me the number without the use of a formula.
Any idea if this is possible?

Greetings.

• Re: getting the sum of filtered data without the use of formulas

If I remember correctly (can't check, phone), The Subtotal() function only sums visible cells.

Code
``````x = application.worksheetfunction.subtotal
(9, Range("A2:A1000"))``````
• Re: getting the sum of filtered data without the use of formulas

Considered using the SUBTOTAL function?

It can be placed anywhere on the spreadsheet, is automatic, and faster than any custom function.

Or, if needed, it can be used as the basis for any VBA solution.

Kieran

• Re: getting the sum of filtered data without the use of formulas

Hi all,

I got this working:

Code
``````Sub test()
Dim mySubtotal As Double

mySubtotal = Application.WorksheetFunction.Subtotal(3, Worksheets("Sheet1").Range("B2:B3"))

'MsgBox mySubtotal

Range("B5").Value = mySubtotal
End Sub``````

My next question would be, how to pass it the range of filtered rows?

Greetings.

• Re: getting the sum of filtered data without the use of formulas

Confused... You said you wanted a Sum, but the parameter '3' tells it to count non blank cells.

You don't pass it the range of just the filtered data, it works on the entire range, summing the visible (ie not hidden by the filter) cells.

As an aside, add 100 to the parameter to also exclude cells hidden manually by setting the row height to 0.

• Re: getting the sum of filtered data without the use of formulas

sorry cytop, just checked that one to see if it works the way. I'm actually dont need to sum up the numbers, just the rows of the filtered data.
I mean, if I use some criteria in a filter to get all the cases that have a certain status for a particular date, it needs to count those rows that appear.
So, if I get 3 rows of data, it means there are 3 cases wiht that criteria.
What can I change so it does not count in the range, but the visible range of rows when filter is applied?

Greetings.

• Re: getting the sum of filtered data without the use of formulas

Hi Cytop,

If I leave it like this:

Code
``````Sub test()
Dim mySubtotal As Double

mySubtotal = Application.WorksheetFunction.Subtotal(3, Worksheets("Raw_Data").Range("A2:A56000"))

'MsgBox mySubtotal

Worksheets("Sheet1").Range("B5").Value = mySubtotal
End Sub``````

It will do a correct job. So, maybe no need to pass the visible rows to it as already said, "it works on the entire range, summing the visible (ie not hidden by the filter) cells"

Next step is to see if it is possible to get 5 values for each day of the workweek but then I just could combine them in one Sub to keep statuses grouped Greetings.

• Re: getting the sum of filtered data without the use of formulas

Hi,

I found this code to do the filtering, but the date criteria that is passed to the filter does actually not filter. If I manually go back to the filter and open the window to edit and press OK button, it filters. Any idea why?
Did I miss something?

Code
``````Sub FilterTo2Fields()
With Sheets("Raw_Data")
.AutoFilterMode = False
With .Range("A1:DB1")
.AutoFilter
.AutoFilter Field:=33, Criteria1:="Being worked on"
.AutoFilter Field:=26, Criteria1:="<=19/08/2015"
End With
End With
End Sub``````

Greetings.

• Re: getting the sum of filtered data without the use of formulas

hi all,
after turning the date around to American style, it actually works. Do I have to change settings some where to get it working? or is the code just simply confusing by it self?

Code
``.AutoFilter Field:=26, Criteria1:="<=8/19/2015"``

Greetings.

• Re: getting the sum of filtered data without the use of formulas

Hi all,

One last question (I hope). How do I pass the value of a cell into the criteria for the date?

Code
``.AutoFilter Field:=26, Criteria1:="<= & Range("D5")"``

????

Greetings.

• Re: getting the sum of filtered data without the use of formulas

Hi all,

I got it working Code
``.AutoFilter Field:=26, Criteria1:="<=" & Worksheets("Sheet1").Range("D5").Value``

Now my last steps are to get the whole workweek from that and spread it out on a row of 5 cells.

Greetings.

• Re: getting the sum of filtered data without the use of formulas

• Re: getting the sum of filtered data without the use of formulas

Hi, sorry for my late reply (I just had some days off)
I will try later on today to attach a dummy workbook.

I have actually another thing, the report will not be 100% correct as the status can change and when I look back at a case lets say 3 months back, if the status was changed in the mean time, that case will not be counted anymore. I need to create each day a snapshot of the previous day so I can have that data for when I want to look in the past.

Now I need to make my code save that snapshot for every day. I will post back when I have something working.

Greetings.

• Re: getting the sum of filtered data without the use of formulas

I all,

I will close this one as I will create it in a different way.

Greetings.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!