Google 电子表格中的 SUMIFS 函数
我试图拥有与 SUMIFS 类似的函数（例如 SUMIF 但具有多个标准）在 Google 电子表格中。 MS-Excel 内置此函数（http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010342933.aspx?CTT=1）。
我尝试使用 ArrayFormula (http://support .google.com/docs/bin/answer.py?hl=en&answer=71291)，类似于 SUMIF：
=ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))
通过添加 AND：
=ARRAYFORMULA(SUM(IF(AND(A1:A10>5,B1:B10=1), C1:C10, 0)))
但是这AND 函数未获取 ArrayFormula 指令并始终返回 FALSE。
我能找到的唯一解决方案是使用 QUERY ，这看起来有点慢而且复杂：
=SUM(QUERY(A1:C10,"Select C where A>5 AND B=1"))
我的目标是用许多值填充一个表（类似于数据透视表）计算：
=SUM(QUERY(DataRange,Concatenate( "Select C where A=",$A2," AND B=",B$1)))
有没有人能以更简单、更快的方式做到这一点？
I'm trying to have a similar function to SUMIFS (like SUMIF but with more than a single criterion) in a Google Spreadsheet. MS-Excel has this function built-in (http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010342933.aspx?CTT=1).
I've tried to use ArrayFormula (http://support.google.com/docs/bin/answer.py?hl=en&answer=71291), similar to the SUMIF:
=ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))
By Adding AND:
=ARRAYFORMULA(SUM(IF(AND(A1:A10>5,B1:B10=1), C1:C10, 0)))
But the AND function didn't pick up the ArrayFormula instruction and returned FALSE all the times.
The only solution I could find was to use QUERY which seems a bit slow and complex:
=SUM(QUERY(A1:C10,"Select C where A>5 AND B=1"))
My Target is to fill up a table (similar to a Pivot Table) with many values to calculate:
=SUM(QUERY(DataRange,Concatenate( "Select C where A=",$A2," AND B=",B$1)))
Did anyone manage to do it in a simpler and faster way?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
在我看来，轻松制作类似 SumIFS 的函数的最简单方法是将 FILTER 和 SUM 函数结合起来。
例如：
解释： FILTER() 过滤 A1:A10 中的行，其中 A1:A10 > 。 5 且 B1:B10 = 1。然后 SUM() 对这些单元格的值求和。
这种方法非常灵活，并且可以轻松地创建 COUNTIFS() 函数（只需使用 COUNT() 而不是 SUM()）。
The simplest way to easily make SumIFS-like functions in my opinion is to combine the FILTER and SUM function.
For example:
Explanation: the FILTER() filters the rows in A1:A10 where A1:A10 > 5 and B1:B10 = 1. Then SUM() sums the values of those cells.
This approach is very flexible and easily allows for making COUNTIFS() functions for example as well (just use COUNT() instead of SUM()).
我发现了一个更快的函数来填充“数据透视表”：
如果没有较重的 String 和 Query 函数，它的运行速度似乎要快得多。
I found a faster function to fill up the "pivot table":
It seems to run much faster without the heavier String and Query functions.
截至 2013 年 12 月，Google 表格现在具有 SUMIFS 函数，如 这篇博文并记录在此处。
请注意，尽管您可以尝试将数据复制粘贴到新工作簿中，但旧电子表格不会转换为新版本。
As of December, 2013, Google Sheets now has a SUMIFS function, as mentioned in this blog post and documented here.
Note that old spreadsheets are not converted to the new version, though you can try copy-pasting the data into a new workbook.
这个人使用 Filter 函数按条件削减数组，然后使用 sum 函数将其全部添加到同一个单元格中。
http://www.youtube.com/watch?v=Q4j3uSqet14
它对我来说就像一种魅力。
This guy used the Filter function to chop down the array by the criteria, then the sum function to add it all in the same cell.
http://www.youtube.com/watch?v=Q4j3uSqet14
It worked like a charm for me.