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.

  • 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


    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.

Participate now!

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