CODEWITHSUNDEEP
excelvbavlookupcountif
Josh Ng
Josh Ng

Reputation: 198

COUNTIFS function working across multiple sheets

enter image description here

How do I include a vlookup into my current set of code to do a countif of all vlookup results across all similar sheets. The codes I have will attempt to perform countif across sheets for one specified cell or a whole range of data in a column or row. Instead, I would like the below function to have the capability to count the number of vlookup result in a column across sheets of similar name.

Function myCountIfSheet1(rng As Range, criteria) As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name Like "Sheet1*" Then
            myCountIfSheet1 = myCountIfSheet1 + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
        End If
    Next ws
End Function



Public Function shifted_lookup(lookup_value As Variant, table_array As Range, column_index As Integer, range_lookup As Integer) As Variant
    Dim curr_wsname As String, oth_wsname As String
    Dim curr_ws As Worksheet, oth_ws As Worksheet
    Set curr_ws = ActiveSheet
    curr_wsname = curr_ws.Name
    oth_wsname = Right(curr_wsname, 3)
    Set oth_ws = Worksheets(oth_wsname)
    Dim src_rng_base As String, src_rng As Range
    src_rng_base = table_array.Address
    Set src_rng = oth_ws.Range(src_rng_base)
    Dim aux As Variant
    shifted_lookup = Application.WorksheetFunction.VLookup(lookup_value, src_rng, column_index, range_lookup)
End Function

Upvotes: 0

Views: 213

Answers (1)

Variatus
Variatus

Reputation: 14383

This ought to do the job. Please try it.

Function myCountIfSheet1(Rng As Range, _
                         Clm1 As Long, _
                         Crit1 As Variant, _
                         Clm2 As Long, _
                         Crit2 As Variant) As Long
    ' 011

    Dim Fun As Long                         ' function return value
    Dim Ws As Worksheet

    For Each Ws In ThisWorkbook.Worksheets
        With Ws
            If .Name Like "Sheet1*" Then
                Fun = Fun + WorksheetFunction.CountIfs( _
                            .Range(Rng.Columns(Clm1).Address), Crit1, _
                            .Range(Rng.Columns(Clm2).Address), Crit2)
            End If
        End With
    Next Ws

    myCountIfSheet1 = Fun
End Function

For ease of calling, I have structured the function call to provide one range address only. In my tests I used A1:D30. Column(A) contained one criterium, Column(D) the other. Of course, column(A) is the first column - Columns(1) - of the range and column D is Columns(4) of the range. So, the following function call would look for "3" in column A and "red" in column D.

Debug.Print myCountIfSheet1(Range("A1:D30"), 4, "red", 1, 3)

The sequence of the criteria is immaterial. You can also add more criteria using the same structure.

Upvotes: 1

Related Questions