具有多个字符串条件的 VBA 中的 SUMIFS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21996167/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SUMIFS in VBA With Multiple String Criteria
提问by William
I have a Sub I am writing that has a SUMIFS statement included in it. I am wanting the sum if to sum a certain column (yearRange.Offset(0,i+3)) when yearRange matches gEvent Value and yearRange.Offset(0, 2) matches gEvent.Offset(0,2) Value. I have tried googling the issue, and checked the MSDN website (which was not very descriptive) and cannot get the function to work. The only possible idea I have is that criteria I am using are Strings rather than Integers. So gEvent.Value may be "ABCD" and gEvent.Offset(0,2) may be "DEFG".
我有一个 Sub 我正在编写，其中包含一个 SUMIFS 语句。当 yearRange 匹配 gEvent 值并且 yearRange.Offset(0, 2) 匹配 gEvent.Offset(0,2) 值时，我想要求和是否对某个列求和 (yearRange.Offset(0,i+3))。我试过在谷歌上搜索这个问题，并检查了 MSDN 网站（描述性不强），但无法使该功能正常工作。我唯一可能的想法是我使用的标准是字符串而不是整数。所以 gEvent.Value 可能是“ABCD”，gEvent.Offset(0,2) 可能是“DEFG”。
When I try to run the code it is breaking here:
当我尝试运行代码时，它在这里中断：
itemPY = Application.WorksheetFunction.SumIfs(yearRange.Offset(0, i + 3), yearRange.Value, "=" & gEvent.Value & """, yearRange.Offset(0, 2), " = " & gEvent.Offset(0, 2).Value & """)
And the error I am receiving is: Run-time error '424': Object required
我收到的错误是：运行时错误“424”：需要对象
Here is the sub in it's complete form:
这是完整形式的子：
Sub CombineData()
Dim eventSheet As Worksheet
Dim yearSheet As Worksheet
Dim yearRange As Range
Dim eventRange As Range
Dim gYear As Range
Dim gEvent As Range
Dim i As Integer
Dim itemCount As Integer
Dim itemPY As Integer
Set eventSheet = ThisWorkbook.Sheets("previousEvent")
Set yearSheet = ThisWorkbook.Sheets("previous12")
Set eventRange = eventSheet.Range("A3", eventSheet.Range("A3").End(xlDown))
Set yearRange = yearSheet.Range("A4", yearSheet.Range("A4").End(xlDown))
For Each gEvent In eventRange
i = 0
gEvent.Offset(0, 16).Value = gEvent.Value
gEvent.Offset(0, 17).Value = gEvent.Offset(0, 1).Value
gEvent.Offset(0, 18).Value = gEvent.Offset(0, 2).Value
itemCount = Application.WorksheetFunction.CountIf(yearRange, gEvent.Value)
For i = 0 To 11
itemPY = Application.WorksheetFunction.SumIfs(yearRange.Offset(0, i + 3), yearRange.Value, "=" & gEvent.Value & """, yearRange.Offset(0, 2), " = " & gEvent.Offset(0, 2).Value & """)
gEvent.Offset(0, 19 + i).Value = itemPY / Item
Next i
Next gEvent
End Sub
Any help I could get would be great.
我能得到的任何帮助都会很棒。
回答by Tim Williams
No need for all that quoting:
不需要所有的引用：
itemPY = Application.WorksheetFunction.SumIfs(yearRange.Offset(0, i + 3), _
yearRange, gEvent.Value, _
yearRange.Offset(0, 2), gEvent.Offset(0, 2).Value)
