17

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.

basic
  • 11,673
  • 2
  • 9
  • 26
Catu
  • 715
  • 1
  • 6
  • 13

4 Answers4

26

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.

Catu
  • 715
  • 1
  • 6
  • 13
1

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.

Felypp Oliveira
  • 2,147
  • 1
  • 17
  • 17
  • 2
    Brings me back to my old life to see this question answered! I haven't heard of MAP I'm gonna check that out for curiosity's sake cheers – Catu Apr 30 '23 at 02:37
0

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)
Damini Suthar
  • 1,470
  • 2
  • 14
  • 43
user19056551
  • 1
-3

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

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • Thanks, but that is my current solution, and so you are correct (forgetting the $ signs though). The formula must be dragged down to apply to all cells. When dealing with new rows every day this is not sustainable. Current formula: `=SUMIFS($A$3:$A$8,$B$3:$B$8,$F$3:$F$8,$C$3:$C$8,"A")` – Catu May 06 '15 at 16:54
  • Are you actually trying to do this in Excel or google docs? In Excel I'd suggest using a table - with a table the formulas are automatically populated to new rows, no dragging required - so you just use whole columns for the ranges and it works however many rows you have. I'm not sure that functionality is available in google though – barry houdini May 06 '15 at 18:08
  • I am using google sheets. I posted as well on the google products forum and some useful answers were proposed. I will answer my own question. Sounds like a good option for excel but I ditched excel years ago, the collaboration and cloud storage are just too important. – Catu May 07 '15 at 15:38