Мне не хватает объяснения, почему что-то не работает, поэтому я и сам отправляю ответ.
Функция SUM
рассчитывает только общее количество ячеек и требует ячейку или диапазон ячеек в качестве параметров.
Функция Concatenate
возвращает текст, а не ячейку или диапазон ячеек.
Поскольку изначально Excel не достаточно мал, чтобы попытаться определить, содержит ли текст ячейку, в Excel появилась функция, которая может преобразовывать текст, содержащий ссылку на ячейку, в фактический диапазон ячеек.
Функция называется indirect
.
Функция indirect
может принимать в качестве входных данных ячейку или текст и возвращать диапазон ячеек.
Ваша формула станет
=SUM($J$1, INDIRECT(CONCATENATE("$J$",VALUE(4-1))))
Или, если вы хотите иметь возможность скопировать ячейку в другую и автоматически транспонировать формулу, она станет
=SUM(J1, INDIRECT(CONCATENATE("J",VALUE(4-1))))