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!

Answer 1

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:

1. The data range B3:D10
2. The row condition --(A3:A10=INDEX(F3#,r))
3. 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.