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

Contributor

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

@Nishkarsh31 

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!

@Nishkarsh31 

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