 # Is it possible to sum individual columns of a spilled range with one formula?

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

3 Replies

# Re: Is it possible to sum individual columns of a spilled range with one formula?

That could be # Re: Is it possible to sum individual columns of a spilled range with one formula?

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!

# Re: Is it possible to sum individual columns of a spilled range with one formula?

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)
)
)``````