Excel 2003 使用多个条件对表格数据求和
我已经在这个问题上苦苦挣扎了一段时间了...我在一张名为 PickupData
有一张表格,我试图对收入(col d)进行求和,其中 org_code(col a) = 第 18 行中的值和月份(col c) = a 列中的值
我尝试过使用 SUMPRODUCT, SUM(IF(blah,IF(blah,x,0),0) (按 ctrl-shift-enter),甚至条件总和向导我认为我不能使用 DSUM,因为它需要为条件参数传递单元格范围,
因为我认为这是这样。可能会影响
谁能提供一个可行的解决方案?
这是测试工作簿:这里
谢谢
李
I've been struggling with this one for a while... I have the following data table on a sheet called PickupData
I then have a sheet where i'm trying to sum income (col d) where org_code (col a) = the value in row 18 and month (col c) = the value in column a
I've tried with SUMPRODUCT, SUM(IF(blah,IF(blah,x,0),0) (pressing ctrl-shift-enter) and even the conditional sum wizard. I don't think i can use DSUM as it requires a cell range to be passed for the criteria parameter.
I also tried un-merging the cells as i thought this could be effecting the results.
Can anyone provide a working solution?
Heres the test workbook: here
Thanks
Lee
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
Sumproduct 适合您:
OrgRef 和 MonthRef 是过滤器地址的占位符。
在您的示例书中,组织代码是文本,而您输入 26 的“Org_Code”字段是数字。他们不会匹配。将您的组织代码转换为数字,或者在 26 前面加上 ' 将其标记为字符串。
Sumproduct will work for you:
OrgRef and MonthRef are placeholders for the address of your filters.
In your example book the Org code is text while your "Org_Code" field where you have 26 entered is a number. They won't match. Either convert your org codes to numbers or preface 26 with a ' to mark it as a string.
您是否尝试过:
或者 Excel 宏?
代码
Have you tried:
or, Excel Macros?
Code
SUM(IF(... 应该可以工作,但是是的,您需要取消合并单元格,因为数组公式在合并单元格中不起作用。在 B19 中输入以下内容应该可以工作(记住 ctrl+shift+enter)
The SUM(IF(... should work, but yes you will need to un-merge your cells because array-formulas do not work in merged cells. The following entered into B19 should work (remembering to ctrl+shift+enter)