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 shift enter.
Can someone solve this?
Thanks.
9 Replies
Try this array formula confirmed with Ctrl+Shift+Enter:
=SUM(IF(COUNTIF(INDIRECT(ADDRESS(10;COLUMN(D10:AW10))&":AW10");">0")<=5;D10:AW10))
- 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.
It works in the workbook that I attached...
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.
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)
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.
