Forum Discussion

James_Buist's avatar
James_Buist
Brass Contributor
Jul 27, 2024
Solved

Generating a 2D array from sumifs on the columns of another 2D array

I have a 2D array of costs by month and want a sumif equivalent based on another dynamic array of Divisions see below. i.e. Jan Div 1 is in M2 so I can refer to this as M2# to get the full table Jan ...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Jul 28, 2024

    James_Buist As a variant, here's a couple of SUMIFS options:

     

    =LET(
    arr, LAMBDA(n, CHOOSE(n, K10#, SEQUENCE(, COLUMNS(M2#)))),
    MAP(arr({1}), arr({2}), LAMBDA(r,c, SUMIFS(INDEX(M2#,, c), K2#, r)))
)

     

    -OR-

     

    =DROP(REDUCE(0, SEQUENCE(COLUMNS(M2#)), LAMBDA(p,c, HSTACK(p, SUMIFS(INDEX(M2#,, c), K2#, K10#)))),, 1)

     

    The latter should be more efficient because there are fewer iterations, but you probably won't notice a difference on a relatively small dataset.

     

    Having said that, I'd be more interested to see how the actual data source is organized. Obviously, cells K2 and M2 contain formulas that reference another dataset, because you're using the spilled range operator (#). If the data source was arranged vertically in a table with columns for "Division", "Month" and "Amount", you could easily summarize the data using a single SUMIFS formula or a pivot table.

     

    Please see the attached example file, if needed...

Harun24HR's avatar
Harun24HR
Bronze Contributor
Jul 28, 2024

James_Buist You can do it by MAKEARRAY() or REDUCE() function. You can do all calculation by a single cell formula. Try the following formula. You can also download the attached file.

=LET(div,UNIQUE(A2:A7),
result,MAKEARRAY(ROWS(div),COLUMNS(C1:G1),LAMBDA(r,c,SUM(FILTER(FILTER(C2:G7,A2:A7=INDEX(div,r)),C1:G1=INDEX(C1:G1,1,c))))),
VSTACK(HSTACK(A1,C1:G1),HSTACK(div,result)))

 

 

James_Buist's avatar
James_Buist
Brass Contributor
Jul 28, 2024

Harun24HR 

Thanks for your solution

As I was not looking for a full table and needed to drive it off the list of Divisions  and all inputs are dynamic arrays – I had to tweak the solution.  My layout means that the list of criteria can’t be generated dynamically from the list at A2# as there may be divisions which have no costs but still need to show. But easy to change. Other than that, it was a good solution and would work well. However, with this solution I also need to include the headers in the function – which is fine – but another input.

For anyone else reading, here is the tweaked result

=LET(div,A10#, MAKEARRAY(ROWS(div),COLUMNS(C2#),LAMBDA(r,c,SUM(FILTER(FILTER(C2#,A2#=INDEX(div,r)),C1#=INDEX(C1#,,c))))))

Many thanks

 

PS. It may be faster than the other solution I chose. 

I will do some performance testing on all three with a much larger dataset then the actual one which is around 200 colums x10 rows. As you stated, I was looking to do this in a single cell which your solution did

 

 

Resources