用于 sumifs 的 vba 工作表函数，其中标准范围在工作表 1 中，标准在工作表 2 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18043219/
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
vba worksheet function for sumifs where criteria range in sheet1 and criteria in sheet 2
提问by vengadasamy
I am trying to a vba code with worksheet function sumifs. I have two sheets, sheet 1 contains criteria range and sheet 2 contains criteria.
我正在尝试使用工作表函数 sumifs 编写 vba 代码。我有两张工作表，工作表 1 包含条件范围，工作表 2 包含条件。
Sheet 1:
第 1 页：
Group Year Cutomer Amount
A 2012 Gold 1000
B 2013 Platinum 1250
C 2012 Gold 1500
B 2012 Platinum 1750
C 2013 Gold 2000
A 2013 Platinum 2250
C 2012 Gold 2500
A 2013 Platinum 2750
B 2012 Gold 3000
Sheet 2:
2012 2013
A Gold
A Platinum
B Gold
B Platinum
C Gold
C Platinum
My code is as follows:
我的代码如下：
Sub SUMIFSTWOSHEETS()
Dim i As Variant
Dim condition As Range
For i = 2 To 7
sheet2.Cells(i, 3) = WorksheetFunction.sumifs(sheet1.Range("d2:d10"), sheet1.Range("a2:a10"), sheet2.Cells(i, 1), sheet1.Range("c2:c10"), sheet2.Cells(i, 2), sheet1.Range("B2:B10"), sheet2.Range("c2"))
sheet2.Cells(i, 4) = WorksheetFunction.sumifs(sheet1.Range("d2:d10"), sheet1.Range("a2:a10"), sheet2.Cells(i, 1), sheet1.Range("c2:c10"), sheet2.Cells(i, 2), sheet1.Range("B2:B10"), sheet2.Range("d2"))
Next i
End Sub
With the above code, vba is running but it gives only zero as answer.
使用上面的代码，vba 正在运行，但它只给出零作为答案。
My answer is as follows:
我的回答如下：
2012 2013
A Gold 0 0
A Platinum 0 0
B Gold 0 0
B Platinum 0 0
C Gold 0 0
C Platinum 0 0
Can somebody help me out?.
有人可以帮我吗？
回答by Ioannis
If I understand your tab placement correctly, try replacing sheet2.Range("c2")with sheet2.Range("c1")and sheet2.Range("d2")with sheet2.Range("d1"). It should give you the following output in Sheet2:
如果我正确理解你的选项卡位置，尝试更换sheet2.Range("c2")用sheet2.Range("c1")和sheet2.Range("d2")用sheet2.Range("d1")。它应该为您提供以下输出Sheet2：
2012 2013
A Gold 1000 0
A Platinum 0 5000
B Gold 3000 0
B Platinum 1750 1250
C Gold 4000 2000
C Platinum 0 0
As a side note, I am not sure why you need ito be Variant: a simple Longshould be OK?
作为一个方面说明，我不知道为什么你需要i是Variant：简单的Long应该是OK？
回答by Robert Co
The values that you are summing should be the last parameter. Thus,
您求和的值应该是最后一个参数。因此，
sheet2.Cells(i, 3) = WorksheetFunction.sumifs(sheet1.Range("a2:a10"), sheet2.Cells(i, 1), sheet1.Range("c2:c10"), sheet2.Cells(i, 2), sheet1.Range("B2:B10"), sheet2.Range("c2"), sheet1.Range("d2:d10"))
My question is why do you need a macro and not just put the formula in Excel.
我的问题是为什么你需要一个宏而不是把公式放在 Excel 中。
