Excel: Use dynamic arrays with SUMIFS to generate a 2D output (based on 1D and 2D inputs)-CodePudding

I am looking to return a 2D dynamic array for SUMIFS, based on 1D and 2D input criteria. I have tried this successfully using BYCOL()/BYROW(), but this only returns 1D arrays by column or by row, whereas I want 2D (so just one formula gives me all the results).

I am trying to use the MAP() function to do this, but I am getting a wrong array size and #N/As. I have also tried combining BYROW() and BYCOL(), but this gives me a #CALC error and I don't think Excel supports this combination.

See below image where I have an example, where I am trying to sum up data for sub-totals by country. The numbers I want to get are at the bottom - but I have done this using cell-by-cell SUMIFS().

Thanks for any help!

CodePudding user response：

It seems if you want to use Map you have to use arrays of the same size and shape. It's a bit of a pain, but you could do something like this just to prove a point:

=LET(range,D2:H6,
countries,B2:B6,
uniqueCountries,UNIQUE(countries),
cols,COLUMN(range),
nRows,ROWS(uniqueCountries),
nCols,COLUMNS(range),
seq,SEQUENCE(nRows,nCols),
HSTACK(uniqueCountries,MAP(IF(seq,uniqueCountries),IF(seq,cols),LAMBDA(a,b,SUM(range*(countries=a)*(cols=b))))))
)

CodePudding user response：

A non-volatile set-up would be less straightforward, though for starters you could consider:

=LET(α,D2:H6,β,B2:B6,γ,SORT(UNIQUE(β)),HSTACK(γ,SUMIFS(OFFSET(INDEX(α,,1),,SEQUENCE(,COLUMNS(α),0)),β,γ)))

If you don't have access to HSTACK then I can provide an alternative.

In the meantime I'll try to find a non-volatile set-up.