В таблицах с месячной разбивкой данные часто растягиваются на годы. Например, у вас есть строка с датами за несколько лет и строки с продажами по месяцам. Нужно посчитать итоговую сумму за весь год, но при добавлении новых месяцев формула не должна съезжать. Обычное СУММЕСЛИ здесь не подойдёт, потому что нужно проверить год каждой даты. Для этого есть функция СУММПРОИЗВ. Она умножает массив условий на массив чисел и суммирует результат — быстро и надёжно.
Шаг 1: Подготовка данных
Таблица содержит:
Строку с датами — в формате дат по месяцам, например: дек. 2024.
Строки с числовыми данными ниже — продажи или другие показатели по месяцам.
В отдельной ячейке указан год, по которому нужно суммировать (например, 2024).
Шаг 2: Записываем формулу СУММПРОИЗВ
В ячейку, где должен быть итог, введите формулу:
=СУММПРОИЗВ((ГОД($C$4:$Z$4)=AB$4)*($C6:$Z6))
Где:
$C$4:$Z$4 — диапазон с датами (абсолютная ссылка, чтобы не менялась при копировании вниз).
AB$4 — ячейка с годом, по которому суммируем (смешанная ссылка: строка зафиксирована, столбец может меняться).
$C6:$Z6 — диапазон с числами для первой строки данных (строка относительная, чтобы формулу можно было скопировать вниз).
Шаг 3: Как это работает
ГОД($C$4:$Z$4) — извлекает год из каждой даты в диапазоне (например, 2023, 2024, 2025).
ГОД($C$4:$Z$4)=AB$4 — сравнивает каждый год с нужным годом (например, 2024). Результат — массив ИСТИНА/ЛОЖЬ.
ИСТИНА/ЛОЖЬ * ($C6:$Z6) — при умножении ИСТИНА становится 1, ЛОЖЬ — 0. В результате в каждом столбце остаётся либо исходное число, либо 0.
СУММПРОИЗВ — суммирует все полученные значения.
Шаг 4: Проверка и копирование
Нажмите Enter — формула покажет сумму за указанный год.
Скопируйте формулу вниз для всех строк с данными (сделайте вставку как формулы, а не как значения).
Хотите увидеть этот процесс вживую и узнать ещё больше полезных советов? Смотрите наглядный пример в нашем Telegram-канале: https://t.me/Natalia_ProExcel/194
Заключение
Функция СУММПРОИЗВ — это надёжный способ суммирования данных по годам, который не ломается при добавлении новых столбцов. Формула автоматически подстраивается под расширение таблицы, если правильно заданы абсолютные ссылки. Идеально для отчётов с месячной детализацией, которые постоянно растут.
Освоить все способы работы с функциями суммирования и анализа данных в Excel можно на курсе «С Excel На ты». Мы показываем, как создавать устойчивые к изменениям формулы.