Mar 18 2021 11:11 AM
I have data spilled in different columns,
I want single cell formula to spill the sum of individual columns in the spilled range.
I'm attaching the file
How can we do that?
@Sergei Baklan @Peter Bartholomew
Mar 18 2021 12:59 PM
Mar 18 2021 02:09 PM
I would take the same approach as @Sergei Baklan
I wrapped my solution up a bit more and did not reference the intermediate calculation, instead going back to the fruit quantity fields in the original table.
= LET(
qty, FILTER(fruitQty, Data[Name]=Name),
qty0, IF(ISNUMBER(qty), qty, 0),
u, SEQUENCE(1,ROWS(qty),1,0),
MMULT(u, qty0) )
I did 'come a cropper' because the FILTER generated blank fields that caused MMULT to error, hence the 'qty0' variable. Also, since I use the beta version of 365, I have a number of λ-functions lying around including ROWTOTAL and COLUMNTOTAL which permits
= LET(
qty, FILTER(fruitQty, Data[Name]=Name),
qty0, IF(ISNUMBER(qty), qty, 0),
COLUMNTOTAL(qty0))
The functions simply act as wrappers for MMULT which, I suspect, is too mathematical for most users. I have also implemented recursive λ-functions for such summations but, at present, that is heavy going!
Mar 22 2021 07:44 AM
I have just opened a fairly recent workbook and noticed that I had implemented
= COLUMNTOTAL(output#)
"where COLUMNTOTAL refers to:"
= LAMBDA(A,
LET(
n, ROWS(A),
u̲, SEQUENCE(1,n,1,0),
MMULT(u̲, A)
)
)