Excel SUMPRODUCT - исключить ячейки заголовка из формулы

Question

У меня есть два столбца, один с датой и один со значениями, и я хочу суммировать все ячейки, где год даты является желаемым годом. Моя проблема в том, что у меня есть заголовки в таблице, и я не хочу вручную определять первую и последнюю строки для рассмотрения, но взять все строки ...

Это не получается с # #VALUE! ошибка:

=SUMPRODUCT((YEAR(C:C)=2015)*(E:E))

Это работает:

 =SUMPRODUCT((YEAR(C2:C1000)=2015)*(E2:E1000))

В C1 и E1 меня есть заголовки (текст). Они отформатированы как текст и в простых формулах, этого достаточно, чтобы исключить их из формулы, но это не работает с SUMPRODUCT ...

Есть ли лучшее решение?

Answer 1

Еще одно, чуть более читаемое предложение.

=SUM(IFERROR((YEAR(C:C)=2015)*E:E,0))

Используйте это как функцию массива (нажмите Ctrl + Shift + Enter, а не просто Enter)

Answer 2

Сразу после публикации у меня появилась идея, и она работает:

Решение:

=SUMPRODUCT((YEAR(INDIRECT("C2:C"&(MATCH(0;E:E;-1));TRUE))=2015)*(INDIRECT("E2:E"&(MATCH(0;E:E;-1));TRUE)))

Функция соответствия находит последнюю ячейку со значением> = 0, для меня это работает нормально. В зависимости от варианта использования, вам придется добавить функцию соответствия ...

Answer 3

Есть ли лучшее решение?

Да, есть.

Используйте таблицы (выберите свой диапазон, а затем: Вставка - Таблица), и там вы можете написать свою формулу, например:

=SUMPRODUCT((YEAR(Table1[HeaderC])=2015)*(Table1[HeaderE]))

Конечно, вместо HeaderC напишите реальный заголовок в столбце C, и то же самое для столбца E.