Last 5 added in a row

[HrtJOr]

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?


Thanks.

[#2]

@HrtJOr

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

Nikolino

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

[#3]

@HrtJOr 

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.

https://exceljet.net/formula/average-last-5-values

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.

[#4]

@NikolinoDE 

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.

[#5]

@HrtJOr 

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

=SUM(IF(COUNTIF(INDIRECT(ADDRESS(10;COLUMN(D10:AW10))&":AW10");">0")<=5;D10:AW10))

[#6]

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.

[#7]

@HrtJOr 

On which Excel version you are? As variant

=SUM(AGGREGATE(14,6,1/(D10:AW10<>"-")*D10:AW10,SEQUENCE(,5)))

[#8]

@HrtJOr 

It works in the workbook that I attached...

[#9]

@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)

[#10]

The problem is solved. Thank you all.