Sum of last 11 numbers

[Kraso]

Hi,
I need a formula that calculates the sum of the last 11 numbers in the line. I found a formula but when I add the 12th number for example a refference error message appears. What I need is to add numbers and to sum up the last 11 (from B to L, from C to M, from D to N, etc.). I'm attaching a file:
Book.xlsx
I'll appreciate your help.

[Ace_XL]

Try

=SUM(INDEX(B2:Z2,COUNT(B2:Z2)-10):INDEX(B2:Z2,COUNT(B2:Z2)))

[Andy Pope]

=sum(index(2:2,1,counta(2:2)-10):index(2:2,1,counta(2:2)))

[benishiryo]

welcome to the forum, Kraso. remove the ",1" in your INDEX formula.
=SUM(INDEX(B2:Z2,MAX(1,COUNT(B2:Z2)-10),1):Z2)
for your current data, you are saying you want row 1 (obtained from MAX of 1 & COUNT-10) & column 1. it works because COUNT is 11. 11-10 is 1. Max is 1

but if you have more 1 more data, it becomes MAX of 1 & 2 (12-10). that will give you 2nd row, 1st column. you don't have a 2nd row in your INDEX formula B2:Z2. that gives you REF error

by omitting the 1, the INDEX guesses that you want it for the column.
=SUM(INDEX(B2:Z2,MAX(1,COUNT(B2:Z2)-10)):Z2)

if it confuses you, you can say you want the 1st row but column to change:
=SUM(INDEX(B2:Z2,1,MAX(1,COUNT(B2:Z2)-10),1):Z2)

[Kraso]

Ace_XL
Andy Pope
benishiryo

Many thanks! I have now 3 working formulas.
Problem solved.