Last 5 added in a row

New Contributor

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 shift enter.

Can someone solve this?


9 Replies



Maybe I am not the right one to suggest a solution to you.

But I am sure if you describe your plan on the file you have sent, you will come up with a proposed solution much faster.


Knowing the Excel version and the operating system would be an advantage


Thank you for your understanding and patience



I know that I don't know anything (Socrates)



Try this formula: =SUM(OFFSET(C7,0,COUNT(D7:AW7),1,-5))


And giving credit where it's due, I found the source for this formula at this website--which I commend to you.

I had to modify it from a formula that calculated the average of the last five rows in a column, and that modification is the one above.


I'll let you read the explanation at the exceljet site. And I also recommend bookmarking that site as a valuable one for self-education.



Numbers will be added in the row however, not every cell will be filled with a number which will be a stripe. I need a formula that provides the sum of the last 5 added numbers. The numbers will not be continuous cell by cell in the row. I'm working with Excel 2010.




Try this array formula confirmed with Ctrl+Shift+Enter:



Well, it must be something like this but still isn't working. I have the formula in a column but this one needs to be in a row. But can't find it out.


On which Excel version you are? As variant



It works in the workbook that I attached...

@Sergei Baklan 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.