How Use COUNTIFS(2007) Function in Excel 2003 (UDF COUNTIFS2003)

   Hi ! Here Suggest to you

[ In 2007 Let's Use 2003 COUNTIFS Use ] Let's Look & Find    =COUNTIFS2003(Range,Condition, Range,Condition, ...)

10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26

A B C D E F Name Worked Bonus Condition 1 Condition 2 Condition 3 Kim Homes 100 kim Homes 200 Sam Branch 110 Count Check Man Office 120 2 2 TRUE KKo Homes 130 2 2 TRUE Kim Branch 140 0 0 TRUE Sam Office 150 3 3 TRUE Man Homes 160 3 3 TRUE KKo Branch 170 0 0 TRUE Kim Office 180 3 3 TRUE Sam Homes 190 3 3 TRUE Man Branch 200 0 0 TRUE KKo Office 210 2 2 TRUE Kim Homes 220 2 2 TRUE Sam Branch 230 3 3 TRUE KKo Branch 290 3 3 TRUE 2600

Used Formula ...(With Running MicrosoftExcel Ver 2003)
No	Addr'	If use below Formula, You'll Get Result as Right	Result	Etc
1	C26	=SUM(C11:C25)	2600
2	D13	=SUMPRODUCT(($A$11:$A$25=D11)*($B$11:$B$25=E11))	2
3	D14	=SUMPRODUCT(($A$11:$A$25="Kim")*($B$11:$B$25="Homes"))	2
4	D15	=SUMPRODUCT(($A$11:$A$25="=D11")*($B$11:$B$25="=E11"))	0
5	D16	=SUMPRODUCT(($A$11:$A$25=D11)*($B$11:$B$25>=E11))	3
6	D17	=SUMPRODUCT(($A$11:$A$25="Kim")*($B$11:$B$25>="Homes"))	3
7	D18	=SUMPRODUCT(($A$11:$A$25="=D11")*($B$11:$B$25>="=E11"))	0
8	D19	=SUMPRODUCT(($A$11:$A$25=D11)*($B$11:$B$25< =E11))	3
9	D20	=SUMPRODUCT(($A$11:$A$25="Kim")*($B$11:$B$25< ="Homes"))	3
10	D21	=SUMPRODUCT(($A$11:$A$25="=D11")*($B$11:$B$25< ="=E11"))	0
11	D22	=SUMPRODUCT(($A$11:$A$25=D11)*($B$11:$B$25< >E11))	2
12	D23	=SUMPRODUCT(($A$11:$A$25="Kim")*($B$11:$B$25< >"Homes"))	2
13	D24	=SUMPRODUCT(($A$11:$A$25=D11)*($C$11:$C$25< =200))	3
14	D25	=SUMPRODUCT(($A$11:$A$25=D11)*($C$11:$C$25< =F11))	3
15	E13	=COUNTIFS2003($A$11:$A$25,D11,$B$11:$B$25,E11)	2
16	E14	=COUNTIFS2003($A$11:$A$25,"Kim",$B$11:$B$25,"Homes")	2
17	E15	=COUNTIFS2003($A$11:$A$25,"=D11",$B$11:$B$25,"=E11")	0
18	E16	=COUNTIFS2003($A$11:$A$25,D11,$B$11:$B$25,">="&E11)	3
19	E17	=COUNTIFS2003($A$11:$A$25,"Kim",$B$11:$B$25,">=Homes")	3
20	E18	=COUNTIFS2003($A$11:$A$25,"=D11",$B$11:$B$25,">="&"E11")	0
21	E19	=COUNTIFS2003($A$11:$A$25,D11,$B$11:$B$25,"< ="&E11)	3
22	E20	=COUNTIFS2003($A$11:$A$25,"Kim",$B$11:$B$25,"< =Homes")	3
23	E21	=COUNTIFS2003($A$11:$A$25,"=D11",$B$11:$B$25,"< ="&"E11")	0
24	E22	=COUNTIFS2003($A$11:$A$25,D11,$B$11:$B$25,"< >"&E11)	2
25	E23	=COUNTIFS2003($A$11:$A$25,"Kim",$B$11:$B$25,"< >Homes")	2
26	E24	=COUNTIFS2003($A$11:$A$25,D11,$C$11:$C$25,"< =200")	3
27	E25	=COUNTIFS2003($A$11:$A$25,"Kim",$C$11:$C$25,"< ="&F11)	3
28	F13	=D13=E13	TRUE
29		F13  His Formula Used This Cell  -> F13:F25
if Formula occurs Error! or Different Result...Check or Remove the Space in Formula.       How about this suggest?

In 2007 Let's Use 2003 COUNTIFS
Use  =COUNTIFS2003(Range,Condition, Range,Condition, ...)

Function COUNTIFS2003(ParamArray var() As Variant)
On Error Resume Next
Dim i7 As Double, LB As Double, UB As Double
Dim adr As Variant
     LB = LBound(var)
     UB = UBound(var)
If UB Mod 2 = 0 Then
     COUNTIFS2003 = "Condition1,2 / 3,4"
     Exit Function
End If
ReDim adr(LB To UB)
For i7 = LB To UB Step 2
     adr(i7) = RT(var(i7), var(i7 + 1))
Next
     
COUNTIFS2003 = "=SUMPRODUCT(--(" & Join(adr, "),--(") & ")"
For i7 = LB To UB
     COUNTIFS2003 = Replace(COUNTIFS2003, "--(),", "")
Next
     COUNTIFS2003 = Replace(COUNTIFS2003, ",--()", ")")
     COUNTIFS2003 = Application.Evaluate(COUNTIFS2003)
End Function



Function RT(vv, cc)
On Error Resume Next
RT = Application.Evaluate(cc)
If CStr(cc) = "" Then
          RT = "" & vv.Address(External:=True) & "),--("
Else
     If Not (IsError(RT)) Then
          If IsEmpty(RT) Then
               RT = vv.Address(External:=True) & "=""" & cc & """"
          ElseIf IsNumeric(RT) Then
               RT = vv.Address(External:=True) & "=" & cc
          ElseIf IsDate(RT) Then
               RT = vv.Address(External:=True) & "=" & cc
          ElseIf TypeName(Range(cc)) = "Range" Then
               If IsNumeric(RT) Then
                    RT = vv.Address(External:=True) & "=" & cc
               Else
                    RT = vv.Address(External:=True) & "=""" & cc & """"
               End If
          Else
               If IsNumeric(RT) Then
                    RT = vv.Address(External:=True) & RT
               ElseIf IsDate(RT) Then
                    RT = vv.Address(External:=True) & RT
               Else
                    RT = vv.Address(External:=True) & "=""" & RT & """"
               End If
          End If
     Else
     Select Case RT
          Case CVErr(xlErrDiv0):   GoTo ers
          Case CVErr(xlErrNA):     GoTo ers
          Case CVErr(xlErrName):   GoTo ers
          Case CVErr(xlErrNull):   GoTo ers
          Case CVErr(xlErrNum):    GoTo ers
          Case CVErr(xlErrRef):    GoTo ers
          Case CVErr(xlErrValue):  GoTo ers
     End Select
     End If
End If
Exit Function
ers:
If Err.Number = 13 Then
     RT = "--(" & vv.Address(External:=True) & "),"""" & cc & """""
Else
     If IsNumeric(cc) Then
          RT = vv.Address(External:=True) & "=" & cc
     Else
          RT = vv.Address(External:=True) & GetRT(cc)
     End If
End If
End Function

Function GetRT(RTstr)
Dim RTtemp1     As String
Dim RTtemp2    As String
Dim RTLeft     As String
     RTLeft = Left(RTstr, 1)
     RTtemp1 = Mid(RTstr, 2)
     RTtemp2 = Mid(RTtemp1, 2)
If IsNumeric(RTtemp1) Then
ElseIf IsDate(RTtemp1) Then
Else
     RTtemp1 = """" & RTtemp1 & """"
End If

If IsNumeric(RTtemp2) Then
ElseIf IsDate(RTtemp2) Then
Else
     RTtemp2 = """" & RTtemp2 & """"
End If

Select Case Left(RTstr, 2)
Case ">=":     GetRT = ">=" & RTtemp2
Case "=>":     GetRT = ">=" & RTtemp2
Case "<=":     GetRT = "<=" & RTtemp2
Case "=<":     GetRT = "<=" & RTtemp2
Case "<>":     GetRT = "<>" & RTtemp2
Case Else
     Select Case RTLeft
     Case "=": GetRT = "=" & RTtemp1
     Case ">": GetRT = ">" & RTtemp1
     Case "<": GetRT = "<" & RTtemp1
     Case Else
          If IsNumeric(RTstr) Then
               GetRT = "=" & RTstr
          Else
               GetRT = "=""" & RTstr & """"
          End If
     End Select
End Select
End Function
Any Bug Or Ask ... Mail Or Post Reply