| [ In 2007 Let's Use 2003 SUMIFS ] Let's Look & Find Use =SUMIFS2003(SumRange, Range,Condition, Range,Condition...) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 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 | G13 | =SUMPRODUCT(($A$11:$A$25=G11)*($B$11:$B$25=H11),$C$11:$C$25) | 320 | |
| 3 | G14 | =SUMPRODUCT(($A$11:$A$25="Kim")*($B$11:$B$25="Homes"),$C$11:$C$25) | 320 | |
| 4 | G15 | =SUMPRODUCT(($A$11:$A$25="=G11")*($B$11:$B$25="=H11"),$C$11:$C$25) | 0 | |
| 5 | G16 | =SUMPRODUCT(($A$11:$A$25=G11)*($B$11:$B$25>=H11),$C$11:$C$25) | 500 | |
| 6 | G17 | =SUMPRODUCT(($A$11:$A$25="Kim")*($B$11:$B$25>="Homes"),$C$11:$C$25) | 500 | |
| 7 | G18 | =SUMPRODUCT(($A$11:$A$25="=G11")*($B$11:$B$25>="=H11"),$C$11:$C$25) | 0 | |
| 8 | G19 | =SUMPRODUCT(($A$11:$A$25=G11)*($B$11:$B$25< =H11),$C$11:$C$25) | 460 | |
| 9 | G20 | =SUMPRODUCT(($A$11:$A$25="Kim")*($B$11:$B$25< ="Homes"),$C$11:$C$25) | 460 | |
| 10 | G21 | =SUMPRODUCT(($A$11:$A$25="=G11")*($B$11:$B$25< ="=H11"),$C$11:$C$25) | 0 | |
| 11 | G22 | =SUMPRODUCT(($A$11:$A$25=G11)*($B$11:$B$25< >H11),$C$11:$C$25) | 320 | |
| 12 | G23 | =SUMPRODUCT(($A$11:$A$25="Kim")*($B$11:$B$25< >"Homes"),$C$11:$C$25) | 320 | |
| 13 | G24 | =SUMPRODUCT(($A$11:$A$25=G11)*($C$11:$C$25< =200),$C$11:$C$25) | 420 | |
| 14 | G25 | =SUMPRODUCT(($A$11:$A$25=G11)*($C$11:$C$25< =I11),$C$11:$C$25) | 420 | |
| 15 | H13 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,G11,$B$11:$B$25,H11) | 320 | |
| 16 | H14 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,"Kim",$B$11:$B$25,"Homes") | 320 | |
| 17 | H15 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,"=G11",$B$11:$B$25,"=H11") | 0 | |
| 18 | H16 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,G11,$B$11:$B$25,">="&H11) | 500 | |
| 19 | H17 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,"Kim",$B$11:$B$25,">=Homes") | 500 | |
| 20 | H18 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,"=G11",$B$11:$B$25,">="&"H11") | 0 | |
| 21 | H19 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,G11,$B$11:$B$25,"< ="&H11) | 460 | |
| 22 | H20 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,"Kim",$B$11:$B$25,"< =Homes") | 460 | |
| 23 | H21 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,"=G11",$B$11:$B$25,"< ="&"H11") | 0 | |
| 24 | H22 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,G11,$B$11:$B$25,"< >"&H11) | 320 | |
| 25 | H23 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,"Kim",$B$11:$B$25,"< >Homes") | 320 | |
| 26 | H24 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,G11,$C$11:$C$25,"< =200") | 420 | |
| 27 | H25 | =SUMIFS2003($C$11:$C$25,$A$11:$A$25,"Kim",$C$11:$C$25,"< ="&I11) | 420 | |
| 28 | I13 | =G13=H13 | TRUE | |
| 29 | I13 His Formula Used This Cell -> I13:I25 | |||
if Formula occurs Error! or Different Result...Check or Remove the Space in Formula. How about this suggest? | ||||
In 2007 Let's Use 2003 SUMIFS
Use =SUMIFS2003(SumRange, Range,Condition, Range,Condition...)
Function SUMIFS2003(sumarr As Range, 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
SUMIFS2003 = "Sum / 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
adr(0) = RT(sumarr, "") & adr(0)
SUMIFS2003 = "=SUMPRODUCT(--(" & Join(adr, "),--(") & ")"
For i7 = LB To UB
SUMIFS2003 = Replace(SUMIFS2003, "--(),", "")
Next
SUMIFS2003 = Replace(SUMIFS2003, ",--()", ")")
SUMIFS2003 = Application.Evaluate(SUMIFS2003)
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
No comments:
Post a Comment