How to specify a range of cells number - 1?

Question

excel

Let's say we have this example:

название	цена руб.
яблоко	20
груша	24
банан	35
итого	79

Now the formula is written in the "total" cell: =СУММ(B2:B4)
I entered the number four manually.
And I need it to be automatic, something like this: =СУММ(B2:B(count(B)-1))
That is, so that instead of 4, let's say, I add 50 lines there, he automatically wrote B2:49.

How to do it?

This is possible, since they conduct budget expenditures there, there must be some kind of function.

Answer 1

Sum is not an array formula, so you can't just use it. I usually use INDEX for such purposes.
It is important that you do not have empty rows in the column, otherwise the formula will not work. For empty values, use 0 instead.
=СУММ(B2:ИНДЕКС(B:B;СЧЕТЗ(B:B);1))
Means the following: the range from B2 to the last cell is summed. The index substitutes this cell from the array. Counts counts the number of non-empty values.

Answer 2

=SUM(INDIRECT("B$2:B"&ROW()-1))
=СУММ(ДВССЫЛ("B$2:B"&СТРОКА()-1))