Sandeep Warrier
Well-known Member
- Joined
- Oct 31, 2008
- Messages
- 2,680
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 2003
Code:
<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">
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 | ||||||||
| 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:
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
