Forum Discussion
Use SUMIFS on Multiple Columns using BYCOL?
We have a BOM template that needs to accommodate multiple end items. On the Purchased items break down we need to list all purchased items then consolidate the list of purchased items (Rows) and maintain the variable number of end items (Columns)
original table of purchased items
| Table1 | ams 1 qty | asm 2 qty | asm 3 qty | ... |
| Purchase Item 1 | 1 | 1 | 2 | |
| Purchase Item 2 | 1 | 1 | ||
Purchase Item 3 | 2 | 5 | ||
| Purchase Item 4 | 5 | 1 | ||
| Purchase Item 3 | 1 | 4 | ||
| Purchase Item 2 | 3 | 1 | 1 | |
| Purchase Item 1 | 4 | 1 |
Consolidated table
| Table2 | ams 1 qty | asm 2 qty | asm 3 qty | ... |
| Purchase Item 1 | 5 | 1 | 3 | |
| Purchase Item 2 | 3 | 1 | 2 | |
Purchase Item 3 | 3 | 9 | ||
| Purchase Item 4 | 5 | 1 |
Using a formula to remove duplicates in the Purchase Items column is easy, that's not my issue.
I can use a SUMIFS of each individual column.
=SUMIFS(INDEX([Table 1 range of all qty values],,1,1),INDEX([Table 1 Purchase Item range],,1),[Table 2 Purchase Item Range])
My issue is getting the SUMIFS to function on all qty columns with out having to copy paste the formula to each column. I would like for it to do this dynamically.
I tried using a BYCOL function with a LAMBDA containing the SUMIFS, but that fails..
=BYCOL((IF([Table 1 Asm list range]>0,1,0)*[Table 1 Asm list range]),LAMBDA(array,SUMIFS(INDEX([Table 1 range of all qty values],,array,1),INDEX([Table 1 Purchase Item range],,1),[Table 2 Purchase Item Range])))
any suggestions?
4 Replies
lori_m Thank you! this is exactly what I needed!
=SUMPRODUCT(($A$2:$A$8=$A11)*($B$1:$D$1=B$10)*$B$2:$D$8)An alternative could be SUMPRODUCT.
As it happens, I have some LAMBDA functions which will be of use to you here.
Please create named functions for these two:
RECURSIVEFILTER:
https://gist.github.com/ncalm/f1c7b5bd8fe86528da88798b545053c8
GROUPAGGREGATE:
https://gist.github.com/ncalm/29c473a940997c3b112bf06e7e71cf15
As you can see, you define a control array which contains either the word group, or the name of an aggregate function you want to use, then you select the table you want to apply the lambda to, select the control array, and it produces the output.
Apart from the "group" fields (of which the function supported nested row headers), these aggregates are supported at time of writing:
{"textjoin", "sum", "min", "max", "counta", "count", "average"}If you'd like to get a better understanding of how it works, you are welcome to read this blog post which explains it in detail:
https://www.flexyourdata.com/blog/quickly-create-summary-tables-in-excel-with-just-one-formula/
