Count Based on a Single Criteria across Multiple Sheets with a Custom Function
The following custom function returns a count based on a single criteria across multiple sheets within a workbook...
Option
Explicit
Function
COUNTIF3D(CritRng As
Range, Crit As
Variant,
ParamArray ArgList() As
Variant)
Dim Arg As Variant
Dim wkb As Workbook
Application.Volatile
If
IsMissing(ArgList) Then
COUNTIF3D
= CVErr(xlErrValue)
Exit Function
End If
Set wkb =
Application.Caller.Parent.Parent
For Each Arg In ArgList
COUNTIF3D
= COUNTIF3D + _
WorksheetFunction.CountIf(wkb.Sheets(Arg).Range(CritRng.Address),
Crit)
Next Arg
End Function
Where to Put the Code
- Open the workbook in which to store the code.
- Open the Visual Basic Editor (Alt+F11).
- Insert a standard module (Insert > Module).
- Copy/paste the above code into the module.
- Return to Microsoft Excel (Alt+Q).
- Save the workbook.
How to Use the Custom Function
| Sheet1 | Sheet2 | Sheet3 | Summary |
 |
 |
 |
 |
The following formulas count the number of times a value in A2:A5 from the specified sheets equals the value in A2 of the Summary sheet...
=COUNTIF3D(A2:A5,A2,"Sheet1","Sheet2","Sheet3")
or
=COUNTIF3D(A:A,A2,"Sheet1","Sheet2","Sheet3")
Note that each sheet name specified in the formula needs to be enclosed within quotes. Also, the sheet names can be listed in any order.
Based on the sample data, the formulas return 4.
Sample Workbook: Download