Forum Discussion
Last 5 added in a row
I am looking for a formula which can sum the last 5 added values in a row. My formula is missing one thing but I can't solve it. I know it's an array formula and needs to be closed with Ctrl shi...
SergeiBaklan I believe your solution gives the 5 largest values.
HrtJOr Assuming by the "last" you want the 5 values entered most to the right then try this modification of Sergei's solution: EDIT: I simplified the solution and added a second option:
=SUM(INDEX($A10:AW10,AGGREGATE( 14,6,1/ISNUMBER(D10:AW10)*COLUMN(D10:AW10),{1,2,3,4,5})))
alternatively here is another approach. since you might not have dynamic arrays I'm hoping one of these will still work for you:
=SUMIF(D7:AW7,">="&AGGREGATE( 14,6,1/ISNUMBER(D10:AW10)*COLUMN(D10:AW10),5)-COLUMN(D10)+1,D10:AW10)
The problem is solved. Thank you all.