Forum Discussion
Combining BYCOL and BYROW
=LET(rng,B3:D8,
VSTACK(
HSTACK("",TOROW(UNIQUE(CHOOSECOLS(rng,2)))),
HSTACK(
UNIQUE(CHOOSECOLS(rng,1)),
IFERROR(
MAKEARRAY(COUNTA(UNIQUE(CHOOSECOLS(rng,1))),COUNTA(UNIQUE(CHOOSECOLS(rng,2))),LAMBDA(r,c,FILTER(CHOOSECOLS(rng,3),(CHOOSECOLS(rng,1)=INDEX(UNIQUE(CHOOSECOLS(rng,1)),r))*(CHOOSECOLS(rng,2)=INDEX(UNIQUE(CHOOSECOLS(rng,2)),c))))),
""))))
MAKEARRAY returns the intended result in my sheet. Otherwise you can combine BYCOL and BYROW by using REDUCE which can spill horizontally and vertically at the same time.
This is the result for range B3:D12:
adidriksen There are various ways to accomplish this, but all will be obsolete soon when the new GROUPBY and PIVOTBY functions are released later this year (hopefully). In the meantime, one option is to use SUMIFS with both a vertical criteria list and a horizontal criteria list to spill the results in a 2D array:
=LET(
proList, TOCOL(UNIQUE(product), 1),
catList, TOROW(UNIQUE(category), 1),
VSTACK(
HSTACK("Sum of Price", catList),
HSTACK(proList, SUMIFS(prices, product, proList, category, catList))
)
)
See attached, if needed...
djclementsThank you for the very efficient solution and for providing context to the topic! I was not aware that the Office team was working on specific functions for this problem, that's great news. The "spilling" functions (FILTER, UNIQUE and so on) and lambda functions recently became available at my company and they've certainly been very empowering so far. I'm very excited to see what the future brings!
