带有Sumifs的Google表格ArrayFormula [英] Google Sheets ArrayFormula with Sumifs

问题描述

通常不需要床单的帮助，但我认为我的大脑因对此进行过多思考而崩溃.

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.

发票有数字.客户付款具有转入"银行帐户以及与之关联的发票编号，因此我知道哪笔付款与哪张发票相对应.有时付款是分期付款的.我想将每个发票和不同帐户的所有付款加起来.我知道如何使用sumifs做到这一点.我想拉的技巧是在第一个单元格中使用一个数组公式来完成此操作.感谢所有帮助.

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:

归功于Google论坛的2n9.这是链接

Credit due to 2n9 from google forums. Here is the link

=arrayformula(sumif(B3:B8&C3:C8,F3:F8&"A",A3:A8))

使用Jean-Pierre Verhulst的查询还有其他一些很好的答案:

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)

这些解决方案中的每一个都以不同的方式解决了问题.它们具有不同的属性，例如删除标题或仅选择特定列.使用指向Google论坛的链接获取更多信息.

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.

希望这对某人有帮助.

这篇关于带有Sumifs的Google表格ArrayFormula的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！