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...
Function COUNTIF3D(CritRng As Range, Crit As Variant, ParamArray ArgList() As Variant)
Dim Arg As Variant
Dim wkb As Workbook
If IsMissing(ArgList) Then
COUNTIF3D = CVErr(xlErrValue)
Set wkb = Application.Caller.Parent.Parent
For Each Arg In ArgList
COUNTIF3D = COUNTIF3D + _
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
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...
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