Count only visible data after filtering

[Stickers]

Hi, what do I need to add to the below code to only count the visible data after filtering. I assume it is possibly .SpecialCells(xlCellTypeVisible) or similar after With Worksheet("calculations"), but I can't figure it out. Help appreciated.

Sub Count_only_visible_data()
With ActiveSheet
.Range("E5").Value = Countv("Work", "WSCA1", "Processed")
.Range("E6").Value = Countv("Work", "WSCA2", "Processed")
.Range("E7").Value = Countv("Work", "SECA11", "Processed")
.Range("E8").Value = Countv("Work", "SECA12", "Processed")
.Range("E9").Value = Countv("Work", "SECA13", "Processed")
.Range("E17").Value = Countv("Work", "NWCA5", "Processed")
.Range("E18").Value = Countv("Work", "NWCA6A", "Processed")
.Range("E19").Value = Countv("Work", "NWCA6B", "Processed")
.Range("E20").Value = Countv("Work", "NWCA7", "Processed")
.Range("E21").Value = Countv("Work", "NWCA8", "Processed")
.Range("E22").Value = Countv("Work", "NWCA9", "Processed")
.Range("E23").Value = Countv("Work", "NWCA10A", "Processed")
.Range("E24").Value = Countv("Work", "NWCA10B", "Processed")
.Range("E32").Value = Countv("Work", "WSCA3", "Processed")
.Range("E33").Value = Countv("Work", "WSCA4", "Processed")
.Range("E34").Value = Countv("Work", "SECA14", "Processed")
.Range("E35").Value = Countv("Work", "SECA15", "Processed")
.Range("E36").Value = Countv("Work", "SECA16", "Processed")
End With
End Sub
Function Countv(v1 As String, v2 As String, Optional v3 As String = "") As Long
Dim rng1 As Range, rng2 As Range, rng3 As Range
With Sheets("calculations")

Set rng1 = .Range("D7:D100000")
Set rng2 = .Range("L7:L100000")
Set rng3 = .Range("M7:M100000")
If Len(v3) > 0 Then
Countv = Application.CountIfs(rng1, v1, rng2, v2, rng3, v3)
Else
Countv = Application.CountIfs(rng1, v1, rng2, v2)
End If
End With
End Function

[paulked]

Hi & welcome to the forum. Untested but try


Function Countv(v1 As String, v2 As String, Optional v3 As String = "") As Long
Dim rng1 As Range, rng2 As Range, rng3 As Range
With Sheets("calculations")

Set rng1 = .Range("D7:D100000").SpecialCells(xlCellTypeVisible)
Set rng2 = .Range("L7:L100000").SpecialCells(xlCellTypeVisible)
Set rng3 = .Range("M7:M100000").SpecialCells(xlCellTypeVisible)
If Len(v3) > 0 Then
Countv = Application.CountIfs(rng1, v1, rng2, v2, rng3, v3)
Else
Countv = Application.CountIfs(rng1, v1, rng2, v2)
End If
End With
End Function

[Stickers]

Hi Paul, that didn't work unfortunately. I received Type Mismatch error below them on the first Countv row

[paulked]

try
WorksheetFunction.CountIfs instead of
Application.CountIfs

[paulked]

Sorry, that probably won't change anything! Can you post you workbook?

[Stickers]

Hi Paul, I have tried to attach it....not sure if it will work.

[paulked]

25406

It worked ok for me with my code in post #2

I changed the output data to Sheet2 as it was going into the main data area.

The results can be seen in the attached.

[Stickers]

Hi Paul, it works unfiltered but when I apply a filter it gives Type Mismatch error on the first Countv = Application.CountIfs(rng1, v1, rng2, v2, rng3, v3) row.

[paulked]

25412

Hi.

I've put a helper column which is 1 for row visible and 0 for hidden and added the condition to the countifs.

[Stickers]

Thanks Paul, I see how I go with it, cheers.

[Stickers]

Thanks Paul, I see how I go with it, cheers.

[snb]

What is the filtercriterion ?
It can be part of any sumproduct formula.

[Stickers]

Hi snb, numerous...last month, this month or change from Processed to Submitted.

[paulked]

Hi snb. I went for =IF(SUBTOTAL(2, T7)>0, 1, 0) rather than sumproduct for the visible helper.

[snb]

In that case I'd prefer a pivottable.

[paulked]

I'll learn about Pivot Tables once I've finished your 'course' on Arrays. Love the simplistic & logical layout of the site, starting to understand the content now :thumb

[snb]

Thanks :hi: