Multi conditional Count across multiple sheets

[Sandeep Warrier]

Hey All,

Looking for a way to do a multi-conditional countif across multiple sheets.

I know the THREED function works, but trying to increase my UDF knowledge.

The UDF below seems to work. Any suggestions/improvements would be appreciated.

Also, is there a way to increase the Conditions part? The current UDF takes only 2 conditions.

I know there is PARAMARRAY, but I'm not sure how to use it in this case.

Excel Workbook
	A	B
3	ABCD	GHI
4	EFGH	JKL
5	ABCD	JKL
6	EFGH	GHI
7	ABCD	GHI
8	EFGH	GHI
9	ABCD	GHI
10	EFGH	JKL
Sheet1

Excel Workbook
	A	B
3	ABCD	GHI
4	EFGH	JKL
5	ABCD	JKL
6	EFGH	GHI
7	ABCD	GHI
8	EFGH	GHI
9	ABCD	JKL
10	EFGH	JKL
Excel 2003 Sheet2

Excel Workbook
	A	B
3	ABCD	GHI
4	EFGH	JKL
5	ABCD	JKL
6	EFGH	GHI
7	ABCD	GHI
8	EFGH	GHI
9	ABCD	JKL
10	EFGH	JKL
Excel 2003 Sheet3

Excel Workbook
	A	B	C	D	E	F
2			UDF	THREED		Sheet Names
3	ABCD	GHI	7	7		Sheet1
4	ABCD	JKL	5	5		Sheet2
5	EFGH	GHI	6	6		Sheet3
6	EFGH	JKL	6	6
Excel 2003 Sheet4

Excel 2003

Cell Formulas
Range	Formula
C3	=Count3DMulSheet($A$3:$A$10,A3,$B$3:$B$10,B3,$F$3:$F$5)
C4	=Count3DMulSheet($A$3:$A$10,A4,$B$3:$B$10,B4,$F$3:$F$5)
C5	=Count3DMulSheet($A$3:$A$10,A5,$B$3:$B$10,B5,$F$3:$F$5)
C6	=Count3DMulSheet($A$3:$A$10,A6,$B$3:$B$10,B6,$F$3:$F$5)
D3	=SUMPRODUCT(--(THREED(First:Last!$A$3:$A$10)=A3),--(THREED(First:Last!$B$3:$B$10)=B3))
D4	=SUMPRODUCT(--(THREED(First:Last!$A$3:$A$10)=A4),--(THREED(First:Last!$B$3:$B$10)=B4))
D5	=SUMPRODUCT(--(THREED(First:Last!$A$3:$A$10)=A5),--(THREED(First:Last!$B$3:$B$10)=B5))
D6	=SUMPRODUCT(--(THREED(First:Last!$A$3:$A$10)=A6),--(THREED(First:Last!$B$3:$B$10)=B6))

Code:

Function Count3DMulSheet(rngCond1 As Range, Cond1 As Variant, rngCond2 As Range, Cond2 As Variant, ShtRng As Range) As Double

    Dim arr1 As Variant
    Dim arr2 As Variant
    Dim arr3 As Variant
    Dim WF As WorksheetFunction
    
    Set WF = Application.WorksheetFunction
    
    arr1 = WF.Transpose(ShtRng)
        
    Count3DMulSheet = 0
    
    For Each arr In arr1
    
        arr2 = WF.Transpose(Sheets(arr).Range(rngCond1.Address))
        For I = 1 To UBound(arr2)
        
            If arr2(I) = Cond1 Then
                arr2(I) = 1
            Else
                arr2(I) = 0
            End If
        
        Next
        
        arr3 = WF.Transpose(Sheets(arr).Range(rngCond2.Address))
        For I = 1 To UBound(arr3)
        
            If arr3(I) = Cond2 Then
                arr3(I) = 1
            Else
                arr3(I) = 0
            End If
        
        Next
        
        Count3DMulSheet = Count3DMulSheet + WF.SumProduct(arr2, arr3)
    
    Next

End Function

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">