Menu
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.
339
Unlike standard formulas, the function is expandable, so it iterates on new data instantly. You can also use ArrayFormula in conjunction with other functions, such as VLOOKUP, FILTER, IF, or SUMIF.
If you want to learn how to use SUMIFS function in Google Sheets, you need to define both sum range from which are values summed and criteria ranges with criterions using the formula: =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, ...], [criterion2, ...]) .
Pressing Ctrl+Shift+Enter while editing a formula will automatically add ARRAYFORMULA( to the beginning of the formula. Note that array formulas cannot be exported.
As you already know, by default, Google Sheets SUMIFS function works with AND logic - all conditions must match to be summed. In some situations, however, you may need to conditionally sum cells with OR logic, when any of the specified criteria is true.
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.
122
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
