Can one do a sumif(s) on a dynamic (spilled) range and return a (2d) array?

Question

Just curious if it's possible to get spill range output from a sum or sumif formula in excel. Given a 2D array, I'd like to sum some rows but preserve the columns. My concern is that once you add a sum or sumif to a spilling formula (sequence, filter, etc.), you lose the spill effect. My interest in doing this is keeping tables automatically updated as the input table changes size.

A picture probably describes this the best: My table has rows with some repeated labels; I'd like to collapse those to a shorter table with unique row labels. For example, B and D rows are repeated, these rows should be summed in the final output. (The output in the example here is manually done, where I do a sumif in each row/column of the output.)

Thanks very much!

@BigBen: great to know that sumifs can spill; I did not know that! You answer gets me partly to my answer - this would take care of the case when the # of rows changes. That's a big help. But if the columns were to change, this would require dragging the formula across columns. Are there any ways around that? Thanks! — JPC, Oct 22 '21 at 21:56

score 0 · Accepted Answer · answered Feb 15 '22 at 08:00

With MAKEARRAY and LAMBDA functions it is possible.

The formula is as follows:

=MAKEARRAY(
   ROWS(F3#),
   COLUMNS(G2#),
   LAMBDA(
      r,
      c,
      SUM(
          B3:D10 * 
          --(A3:A10=INDEX(F3#,r)) * 
          --(B2:D2=INDEX(G2#,1,c))
      )
   )
)

The MAKEARRAY formula spans an array with the required number of rows and columns via the first two parameters ROWS(F3#) and COLUMNS(G2#) (note that F3# = F3:F8 and G2# = G2:I2).

The third parameter of MAKEARRAY takes the LAMBDA function and provides provides the number of rows r and columns c to its first two parameters.

The third parameter of LAMBDA takes here the SUM function to simulate a SUMIFS calculation. It has three factors:

The data range B3:D10
The row condition --(A3:A10=INDEX(F3#,r))
The column condition --(B2:D2=INDEX(G2#,1,c)) The conditions in 2) and 3) result to 1 or 0 when applicable or not. So the SUM counts a value where it matches and 0 where it does not. Besides, the formulas in 2) and 3) are using row number r and column number c with INDEX to obtain the condition values.

Can one do a sumif(s) on a dynamic (spilled) range and return a (2d) array?

1 Answers1

Linked