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

Hi all,

I got this working:

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

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:

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?

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?

.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?

.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

.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

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.