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

%3CLINGO-SUB%20id%3D%22lingo-sub-2220745%22%20slang%3D%22en-US%22%3EIs%20it%20possible%20to%20sum%20individual%20columns%20of%20a%20spilled%20range%20with%20one%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2220745%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20data%20spilled%20in%20different%20columns%2C%3CBR%20%2F%3EI%20want%20single%20cell%20formula%20to%20spill%20the%20sum%20of%20individual%20columns%20in%20the%20spilled%20range.%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20attaching%20the%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20we%20do%20that%3F%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2220745%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2220974%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20sum%20individual%20columns%20of%20a%20spilled%20range%20with%20one%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2220974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523656%22%20target%3D%22_blank%22%3E%40Nishkarsh31%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20643px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F265354iEA63367BBCDD508C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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)
      )
   )