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...
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...
On which Excel version you are? As variant
=SUM(AGGREGATE(14,6,1/(D10:AW10<>"-")*D10:AW10,SEQUENCE(,5)))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)
