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.
CodePudding user response:
A simply old fashion Sumif function is enough.
In I2
, formula copied down :
=SUMIF(A:E,H2,B:F)
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():
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:
And just for fun playing with date control, still using your format: