CODEWITHSUNDEEP
google-sheetssumifsarray-formulas
Catu
Catu

Reputation: 815

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.

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.

Upvotes: 20

Views: 46948

Answers (4)

Felypp Oliveira
Felypp Oliveira

Reputation: 2197

Nowadays, your SUMIFS formula works well in a MAP function, as shown below:

The MAP function works like a loop in the company invoices, executing a custom function LAMBDA for each invoice. Your SUMIFS formula is the body of this custom function and requires a minor change to work properly: its criteria F8, related to the invoice filter, need to be replaced by the invoice input parameter from the lambda, which I named invoice.

Last, but not least, the MAP function itself applies for the entire column of invoices, even on blank cells. To avoid this behaviour, wrap the column reference in an ARRAY_CONSTRAIN function and set the desired number of rows and columns to be evaluated. In this case, it's a single column, and the number of rows can be calculated using a COUNTA function on the entire invoices column.

The formula shown in the image does everything explained above.

Upvotes: 2

user19056551
user19056551

Reputation: 1

I have added a column to combine the two conditions that need to match in both tables.

I used an arrayformula to extend the combined conditions, then I used a sumif inside of another arrayformula to do what would have effectively been sumifs within an arrayformula.

Table 1 (Paint Estimate)

=ARRAYFORMULA(IFERROR(IF(E2:E<>"",E2:E&"First Coat",0),"E"))
=ARRAYFORMULA(IFERROR(SUMIF(Progress!B2:B,T2:T,Progress!G2:G),0))

Table 2 (Progress)

=ARRAYFORMULA(A2:A&E2:E)

Upvotes: 0

Catu
Catu

Reputation: 815

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.

Upvotes: 26

barry houdini
barry houdini

Reputation: 46451

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....

Upvotes: -3

Related Questions