带有 Sumifs 的 Google Sheets ArrayFormula [英] Google Sheets ArrayFormula with Sumifs
问题描述
通常不需要帮助处理床单，但我认为我的大脑因为想太多而崩溃.
尝试使用数组公式填充整列，该公式根据其他两列的条件对单独列中的值求和.如果这听起来很奇怪，请查看
发票有数字.客户付款已进入"银行账户以及与它们相关的发票编号，所以我知道哪个付款对应哪个发票.有时付款是分批进行的.我想汇总每张发票和不同帐户的所有付款.我知道如何使用 sumifs 来做到这一点.我想使用的技巧是在第一个单元格中使用一个数组公式.感谢所有帮助.
我最终使用的解决方案是这样的:
归功于来自谷歌论坛的 2n9.这是链接
=arrayformula(sumif(B3:B8&C3:C8,F3:F8&"A",A3:A8))
使用 Jean-Pierre Verhulst 的查询还有其他一些非常好的答案:
=query(A2:C8, "select B, sum(A) group by B pivot C")
=query(query(A2:C8, "select B, sum(A) group by B pivot C"), "select Col2, Col3")
=ArrayFormula(query(query(A2:C8, "select B, sum(A) group by B pivot C"), "select Col2, Col3 offset 1",0)+0)
这些解决方案中的每一个都以不同的方式解决了问题.它们具有不同的属性，例如删除标题或仅选择特定列.使用指向 google 论坛的链接了解更多信息.
希望这对某人有所帮助.
Usually don't need help with sheets but I think my brain is imploding from thinking on this too much.
Trying to fill an entire column with an array formula that sums values from a separate column based on conditions from two other columns. If that sounds strange just check out this example sheet.
Invoices have numbers. Customer payments have "Into" bank accounts and also invoice numbers associated with them so I know which payment corresponds to which invoice. Sometimes payments are made in pieces. I would like to sum all payments for each invoice and on separate accounts. I know how to do this using sumifs. The trick I want to pull is doing this with one array formula in the first cell. Appreciate all help.
The solution that I ended up using was this:
Credit due to 2n9 from google forums. Here is the link
=arrayformula(sumif(B3:B8&C3:C8,F3:F8&"A",A3:A8))
There were some other very good answers there using queries from Jean-Pierre Verhulst:
=query(A2:C8, "select B, sum(A) group by B pivot C")
=query(query(A2:C8, "select B, sum(A) group by B pivot C"), "select Col2, Col3")
=ArrayFormula(query(query(A2:C8, "select B, sum(A) group by B pivot C"), "select Col2, Col3 offset 1",0)+0)
Each of these solutions solves the problem but in a different way. They have different attributes such as removing headers or choosing only a certain column. Use the link to the google forum for more information.
Hope this helps someone.
这篇关于带有 Sumifs 的 Google Sheets ArrayFormula的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
