sumifs 函数的 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23603464/
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 code for sumifs function
提问by user3614197
I have the following code and I get run-time error '1004' is it to do with text as the variables? Ive tried also refering to the cells that contain my condition directly but have the same problem
我有以下代码，但出现运行时错误“1004”是否与文本作为变量有关？我也试过直接引用包含我的条件但有同样问题的单元格
Sub sumifs()
Dim Month_tally As Double
Dim Monthrange As Range
Set Monthrange = Range("B2:D5")
Set months = Range("B1:D1")
Set Prospects = Range("A2:A4")
Range("B6").Formula = WorksheetFunction.sumifs(Monthrange, months, (May), Prospects, (CN))
End Sub
May June July
CN 456 2 2 Nj 2 2 2 BH 2 456 5647 BG 4567 2 2
CN 456 2 2 Nj 2 2 2 BH 2 456 5647 BG 4567 2 2
回答by Apropos
Your user defined function may be named ending with 's' but the Excel worksheet function is simply 'sumif' and takes just 3 parameters: range, criteria, sum_range. Perhaps you're actually trying to do this:
您的用户定义函数可以命名为以“s”结尾，但 Excel 工作表函数只是“sumif”，并且仅采用 3 个参数：范围、条件、sum_range。也许您实际上是在尝试这样做：
Range("B6").FormulaR1C1 = "=SUM( OFFSET( R[-4]C[-1], 0, MATCH(""May"", R[-5]C:R[-5]C[2], FALSE), 4, 1 ) )"
EDIT
编辑
OK, try this instead:
好的，试试这个：
Sub sumifs()
'Dim Month_tally As Double
Dim Monthrange As Range
Dim Months As Range
Dim CriteriaRange As Range
Set Monthrange = Range("A1:D5") 'Include the first column and header row
Set Months = Range("B1:D1")
Set CriteriaRange = Range("A10:A11")
'Set Prospects = Range("A2:A4")
Range("A1").Value = "Prospects" ' just to ensure first column has header
Range("A10").Value = "Prospects"
Range("A11").Value = "CN"
Range("B10").Value = "Column to sum"
Range("B11").Value = "May"
Range("B6").Value = WorksheetFunction.DSum(Monthrange, Range("B11"), CriteriaRange)
End Sub
Of course, it becomes trivial via a Pivot Table. Can you get the source data structured as 3 columns: Prospects, Month, ProdQty?
当然，它通过数据透视表变得微不足道。您能否将源数据结构化为 3 列：Prospects、Month、ProdQty？
回答by Patrick Honorez
just remove the "s" at end of WorksheetFunction.sumifsand use the correct syntax.
只需删除末尾的“s”WorksheetFunction.sumifs并使用正确的语法。
