Feb 02 2021 07:14 AM
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.
Feb 02 2021 07:46 AM
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)
Feb 02 2021 07:56 AM - edited Feb 02 2021 07:57 AM
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.
Feb 02 2021 07:59 AM
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.
Feb 02 2021 08:00 AM
Try this array formula confirmed with Ctrl+Shift+Enter:
=SUM(IF(COUNTIF(INDIRECT(ADDRESS(10;COLUMN(D10:AW10))&":AW10");">0")<=5;D10:AW10))
Feb 02 2021 08:11 AM
Feb 02 2021 08:18 AM
On which Excel version you are? As variant
=SUM(AGGREGATE(14,6,1/(D10:AW10<>"-")*D10:AW10,SEQUENCE(,5)))
Feb 02 2021 09:00 AM - edited Feb 02 2021 09:31 AM
@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)