COUNTIF function for filtered tables

[haplc]

Dear Madame /Sir

I am using an excel sheet where I am applying auto filters using VBA. In Column 6 , I have type of training (eLearning, InClass, Webex) and in column 7, I have time required for the training. After applying filter I am able to see how many hours of training required for selected filter using this code:

mrange = ActiveWorkbook.Sheets("Jan 2015").AutoFilter.Range.Address
Myval = ActiveWorkbook.Sheets("Jan 2015").Range("" & mrange & "").Columns(6).SpecialCells(xlCellTypeVisible).Count

Similarly I can sum of training hours using this code:
thours = Application.WorksheetFunction.Sum(Range("" & mrange & "").Columns(7).SpecialCells(xlCellTypeVisible))

Now I want to calculate hours and number of training only for one type that is: want to make count if function for the filtered table
This means, want to calculate how many eLearning training are available in column 6 or how many hours are required for elreaning type pf training (summing up hours in column 7 for elearning type of training)

I have tried count if function, but it is always returning the value by counting all rows and not filtered rows.

Any help would be greatly appreciated

Thanks in advance

With best regards

 

[pgc01]

Hi

You can loop through the visible cells and test each one, like:

' only sum visible cells in column 7 when cell in column 6 is equal to "Type1"

Dim rRng As Range, rCell As Range
Dim dSum As Double

Set rRng = ActiveWorkbook.Sheets("Jan 2015").AutoFilter.Range.Columns(6).SpecialCells(xlCellTypeVisible)
For Each rCell In rRng
    If rCell.Value = "Type1" Then dSum = dSum + rCell.Offset(0, 1).Value
Next rCell

MsgBox dSum

 

[haplc]

Thanks a lot: It is working
For counting , I have used following (based on your code for summing)
If rCell.Value = "RolePlay" Then tcountrp = tcountrp + 1
Next rCell

 

[pgc01]

You're welcome. Thanks for the feedback.