我正在尝试使用多个条件求和:
“获取”(A列)
“是”(C列)
总和栏K
从下图可以看出,我设法在excel中使用SumProduct得到了答案。当我尝试使用VBA使用SumProduct获得相同的答案时,我收到一条错误消息13 Type Mismatch。
图片:
有什么建议吗?
码:
Option Explicit
Sub test()
Dim Criteria1 As String, Criteria2 As String
Dim Lastrow As Long, Result As Double
Dim rng1 As Range, rng2 As Range, rng3 As Range
With ThisWorkbook.Worksheets("Sheet1")
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng1 = .Range("A3:A" & Lastrow)
Set rng2 = .Range("C3:C" & Lastrow)
Set rng3 = .Range("K3:K" & Lastrow)
Criteria1 = "Get"
Criteria2 = "Yes"
Result = Application.Evaluate("SumProduct(--(rng1 = Criteria1),--(rng2 = Criteria2),--rng3))")
End With
End Sub
最佳答案
三件事rng1, rng2, rng3, Criteria1 and Criteria2
是变量。当您将它们放在双引号中时,它们将成为一个字符串。
您已将result
声明为Double
。将其声明为Variant
。这导致不匹配错误。
您实际尝试的公式是=SUMPRODUCT(--($A$1:$A$3 = "Get"),--($C$1:$C$3 = "Yes"),--$K$1:$K$3)
。条件应在其周围加上双引号。
这是您要尝试的吗? (未试)
Result = Application.Evaluate("SumProduct(--(" & _
rng1.Address & _
" = """ & _
Criteria1 & _
"""),--(" & _
rng2.Address & _
" = """ & _
Criteria2 & _
"""),--" & _
rng3.Address & _
")")
关于excel - SumProduct公式错误13类型不匹配,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/54881261/