Countif across multiple sheets with criteria/parameters

Question

I have a weekly grading spread sheet with 5 different categories in columns b, e, h, k, & n in which I create a new copy of the original sheet every week and enter a yes or no for that week. I want to compile all of these grades on a summary sheet so that sheet 1 counts the number of "y"s in sheets "sheet2" through "last" in each respective column. Then I can divide by the number of sheets in that range to get a percentage of y's entered in each column b, e, etc. I am pretty new to creating functions and using vba so I am really struggling.

The trickiest part is I would like the function to be able to accept the following arguments/parameters: which sheet to start counting at, which sheet to stop counting at, what to count (in this case y, but could change in my next workbook or if I add a new category), what cell to count from since I want to count a single cell on each sheet so that b7 is a compilation of only b7's on the range of sheets.

This was my feeble attempt:

Function mycountif(start As Variant, last As Variant, Cell As Variant, criteria As Variant) 'define name and parameters

Dim count As Integer

count = 0

For Each Worksheet In Worksheet.range(start, last)
count = count + Application.WorksheetFunction.CountIf(range(Cell), criteria)

Next Worksheet

mycountif = count

End Function

I'm new to the community so please let me know if there is anything else I need to clarify.

Thank you so much for any help you can lend.

Answer 1

Not knowing what the rest of your code is like, and assuming that the part you need help on is what's posted above:

Try this out to get your ws count working.

A few notes about your code:

• You have a function but it isn't set to return anything. You need to state what it returns after the arguments.

example:

Function mycountif(args1, arg2, arg3, arg4) As Long  

• Long Type is better for count than integer.
• To loop through worksheets, you will want to declare the variable you are using, ws, as a Worksheet, then loop through the Worksheets, and use an IF statement to selectively use them instead of only looping through the ones that meet the criteria, loop all then test each.
• I have to assume you are passing valid variables into this, and know why and how you are using them.

Example:

Function mycountif(start As Variant, last As Variant, Cell As Variant, criteria As Variant) As Long

'define name and parameters
Dim ws As Worksheet
Dim count As Long

    count = 0

    For Each ws In Worksheets
        If ws.Index >= start And ws.Index <= last Then
            count = count + Application.WorksheetFunction.CountIf(Range(Cell), criteria)
        End If
    Next ws

    mycountif = count

End Function

You could also test the worksheet names. For example, if you had sheet1, sheet2, sheet3.

'Convert the result of extracting the right character from the ws.Name into sheetNum As Long.
    sheetNum = CLng(Right(ws.name, 1))
'Then test the sheetNum

Another option would be to add a tag to each worksheet you want to use, and then test for the tag. 'If ws.Tag = something Then'

Here is a link to all the available Worksheet Properties you can access.

Answer 2

You are close with the for each but unfortunately, you can't loop that way.
By looping through all the sheets, and switching a count on (and then off) between sheets, then you can decide which ones to count.
We also need to keep a count of the sheets that we include in our count, so we can get the average afterwards.

This is what I came up with:

Option Explicit

Function MultiCountIf(criteria, CommonRange As String, StartSheet As String, EndSheet As String) As Variant
Dim LoopVar As Long
Dim Tot As Long
Dim CountThis As Boolean
Dim NumSheets As Long

Tot = 0
CountThis = False
NumSheets = 0

For LoopVar = 0 To Sheets.Count
    If Sheets(LoopVar).Name = StartSheet Then CountThis = Not CountThis
    If CountThis Then
        NumSheets = NumSheets + 1
        Tot = Tot + WorksheetFunction.CountIf(Sheets(LoopVar).Range(CommonRange), criteria)
    End If
    If Sheets(LoopVar).Name = EndSheet Then CountThis = Not CountThis
Next LoopVar

If NumSheets = 0 Then
    MultiCountIf = CVErr(xlErrDiv0)
Else
    MultiCountIf = Tot / NumSheets
End If
End Function