Geek Answers Handbook

Google Sheets ArrayFormula with Sumifs

Usually you do not need help with the sheets, but I think that my brain is swollen from thinking about it too much.

An attempt to fill an entire column with an array formula that sums the values ​​from a separate column based on conditions from two other columns. If that sounds weird, just check out this sample sheet .

screenshot

Invoices are numbered. Customers have Into bank accounts, as well as account numbers associated with them, so I know what payment corresponds to the account. Sometimes payments are made in pieces. I would like to summarize all payments for each account and individual accounts. I know how to do this using sumifs. The trick I want to extract does this with a single array formula in the first cell. Appreciate all the help.

+5
source share
2 answers

The solution I used was the following:

2n9 credit from google forums. Here's a 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 deleting headings or selecting only a specific column. For more information, use the google forum link.

Hope this helps someone.

+6
source

You can use SUMIFS to return an array, for example. in L3

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

Note that when one criterion is usually expected, I used F3: F8, so the formula returns the required array of 6 values

Unconfirmed as I am in my ipad and it does not seem to look like google docs ....

-1
source

More articles:


All Articles