Google Sheets ArrayFormula with Sumifs

14
votes

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.

screenshot

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.

2
google-sheetssumifsarray-formulas

2 Answers

20
votes

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.

-2
votes

You can use SUMIFS to return an array, e.g. in L3

=SUMIFS(A3:A8,B3:B8,F3:F8,C3:C8,"A")

Note that where a single criteria is normally expected I have used F3:F8, so the formula returns the required array of 6 values

Untested as I'm currently on my ipad and it doesn't seem to like google docs....