如果有多个条件，则 VBA 求和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20301709/
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 Sum If With Multiple Criteria
提问by IRHM
I wonder whether someone may be able to help me please.
我想知道是否有人可以帮助我。
I've been trying, for about a week now to put together a sheet which encompasses a 'SumIf' Formula based on a multiple criteria.
大约一个星期以来，我一直在尝试将包含基于多个标准的“SumIf”公式的表格放在一起。
I've been able to do this, manually entering the formula with the desired results in a cell range.
我已经能够做到这一点，在单元格范围内手动输入具有所需结果的公式。
The problem I have is really two fold.
我遇到的问题实际上有两个方面。
- The formula I used was an array formula, but the skill level of some of my users is perhaps not as good as I would hope, so I know they won't be able to cope adding the formula themselves, so I looked at the option of using VB code,
- The problem with this is, I know you cannot use an array formula in VB, so I'm a little unsure how to proceed.
- 我使用的公式是数组公式，但我的一些用户的技能水平可能没有我希望的那么好，所以我知道他们无法自己添加公式，所以我查看了选项使用VB代码，
- 问题是，我知道你不能在 VB 中使用数组公式，所以我有点不确定如何继续。
To perhaps illustrate this a little better, I've attached a file hereshows what I'm trying to achieve.
为了更好地说明这一点，我在此处附加了一个文件，显示了我正在努力实现的目标。
- On the sheet "Slide 5", I'm would like to extract the results in the columns shaded orange,
And for each cell I would like to perform the following 'SumIf':
- Sum the figure in the named range "Actuals",
- If the value in named range "JRole"is the same as the value in row 6of the
"Slide 5"sheet, and, - The value in the named range "Months"is the same as the value in cell B3on
the "Slide 5"sheet, and, - The value in the named range "PLOB"is the same as the value in the cell in
column Kon the "Slide 5"sheet.
- 在“幻灯片 5”工作表上，我想提取橙色阴影列中的结果，
对于每个单元格，我想执行以下“SumIf”：
- 对命名范围"Actuals" 中的数字求和，
- 如果命名范围“JRole”中的值与“Slide 5”表第6行中的值相同，并且，
- 命名范围“月份”中的值
与“幻灯片 5”工作表上单元格B3 中的值相同，并且，
- 命名区域“PLOB”中的值与“幻灯片 5”工作表上
K 列单元格中的值相同。
As I say, I've been working on this for a while, but I just can't come up with a solution.
正如我所说，我已经为此工作了一段时间，但我只是想不出一个解决方案。
I just wondered whether someone could possibly look at this please and offer some guidance on how I may be able to achieve this.
我只是想知道是否有人可以看看这个，并就我如何实现这一目标提供一些指导。
Many thanks and kind regards
非常感谢和亲切的问候
回答by
If you wanted to run a Sub to populate the orange fields then my code would fill in the data for you
如果您想运行一个 Sub 来填充橙色字段，那么我的代码将为您填写数据
Option Explicit
Sub GetSolution()
' declaring a variable of Worksheet type for easier reference
' instead of saying: Sheets("All Data") you can use a shorter
' variable name: "data"
Dim data As Worksheet
Set data = Sheets("All Data")
Dim result As Worksheet
Set result = Sheets("Slide 5")
' these two variables hold the SUM for both analyst types
' in the loops flow
Dim seniorAnalystSum As Double
Dim analystSum As Double
' using resource and projectLob Range objects for quick reference to cells
' in both spreadsheets
' so again, instead of saying data.Range("A" & i) etc
' simple and short way is "resource" and/or "projectLob"
Dim resource As Range
Dim projectLob As Range
' for each cell in between K8:K16 on the result/ Sheets("Slide 5")
' pick up one cell at a time
For Each resource In result.Range("K8:K16")
' and go over all cells between B5 and last cell in Data sheet
For Each projectLob In data.Range("B5:B" & data.Range("B" & Rows.Count).End(xlUp).Row)
If projectLob = resource Then
' compare year and month between the matching cells
If (Month(projectLob.Offset(0, 8)) = Month(result.Range("B3"))) And _
(Year(projectLob.Offset(0, 8)) = Year(result.Range("B3"))) Then
' compare to the row of the table that holds the type of analyst
If projectLob.Offset(0, 7) = result.Range("L6") Then
'C&R Senior Analyst
seniorAnalystSum = seniorAnalystSum + projectLob.Offset(0, 12)
ElseIf projectLob.Offset(0, 7) = result.Range("N6") Then
'C&R Analyst
analystSum = analystSum + projectLob.Offset(0, 12)
End If
End If
End If
Next
' Assigns the sum for the senior analyst
resource.Offset(0, 2) = seniorAnalystSum
' assigns the sum for normal analyst
resource.Offset(0, 4) = analystSum
' reset both sums for the next loop
seniorAnalystSum = 0
analystSum = 0
Next
End Sub
I have used your sample data and populated the results as follows
我使用了您的示例数据并按如下方式填充了结果
I hope this is the kind of solution you're looking for :) Awaiting your feedback
我希望这是您正在寻找的解决方案 :) 等待您的反馈
回答by Youbaraj Sharma
Use the following formula in SLIDE5 COLUMN M
使用下面的公式 SLIDE5 COLUMN M
=SUMPRODUCT(('All Data'!$I:$I='Slide 5'!$L:$M)*('All Data'!$J:$J='Slide 5'!$B)*('All Data'!$B:$B='Slide 5'!$K8)*('All Data'!$N:$N))
and in SLIDE5 COLUMN OUSE THIS
并在SLIDE5 COLUMN O使用这个
=SUMPRODUCT(('All Data'!$I:$I='Slide 5'!$L:$M)*('All Data'!$J:$J='Slide 5'!$B)*('All Data'!$B:$B='Slide 5'!$K8)*('All Data'!$N:$N))
Juse drag the formula to the subsequent rows
只需将公式拖到后续行
回答by Guilhem Hoffmann
quick and dirty solution
快速而肮脏的解决方案
1 - Use the evaluateoperator to get your expected result
1 - 使用评估运算符来获得预期的结果
Evaluate('SUMPRODUCT(
('All Data'!$I:$I='Slide 5'!$L:$M)
*('All Data'!$J:$J='Slide 5'!$B)
*('All Data'!$B:$B='Slide 5'!$K8)
*('All Data'!$N:$N))'
)
2 - Use the Application.WorksheetFunction.Sumproductfunction which replace the SUMPRODUCT wokrksheet function
2 - 使用Application.WorksheetFunction.Sumproduct函数替换 SUMPRODUCT wokrksheet 函数
