Home > Blockchain >  Is it possible to create a SUM of all of the unique names 2 dimensionally and their value like in th
Is it possible to create a SUM of all of the unique names 2 dimensionally and their value like in th

Time:05-04

I am trying to find a function that will look through all of the sheet to find unique names and sum the value that was assigned at a specific date like below: (End result should be like the summary) - I am restricted to use this format and can't have all the names under one column and dates under another. enter image description here

CodePudding user response:

A simply old fashion Sumif function is enough.

In I2, formula copied down :

=SUMIF(A:E,H2,B:F)

enter image description here

Formula in H3:

=LET(X,WRAPROWS(TOCOL(A3:F5,1),2),Y,INDEX(X,0,1),Z,UNIQUE(Y),HSTACK(Z,BYROW(Z,LAMBDA(a,SUM((Y=a)*INDEX(X,0,2))))))

Note: The above is based on the assumption you don't know the unique names up front (as stated in the body of your question). Admittingly, if these are known, there are other/better answers.

CodePudding user response:

So laid the data out like so and used sumifs():

enter image description here

SUMIFS($C$2:$C$8,$B$2:$B$8,F2)

Will let you consider adding a date control: prior to, equal to or after...

If you say you cannot change the source data, then make a data sheet to copy all the data, do the results like I show then link the results over...

So if you have to keep the format (instead of copying the data as already suggested - perhaps you missed that as you did not address that in your comment) then you could use sumifs() multiple times like so:

enter image description here

And just for fun playing with date control, still using your format: enter image description here

  • Related