Общая формула
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Резюме
Для суммирования по месяцам вы можете использовать формулу, основанную на функции СУММЕСЛИМН, с помощью функции EOMONTH. В показанном примере формула в F4:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
В этой формуле используются именованные диапазоны «сумма» (C4: C9) и «дата» (B4: B9).
Объяснение
Функция СУММЕСЛИМН может суммировать значения в диапазонах на основе нескольких критериев.
В этом случае мы настраиваем СУММЕСЛИМН для суммирования сумм по месяцам с использованием двух критериев: один соответствует датам, которые больше или равны первому дню месяца, второй - датам, меньшим или равным последнему дню месяца. Другими словами, нам нужна такая формула:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Или более пуленепробиваемая альтернатива:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Эта версия более надежна, поскольку даты строятся с отдельными значениями года, месяца и дня и не вводятся как текст, который должен интерпретироваться Excel.
Обычно это неудобно, потому что, если вы добавляете названия месяцев в виде текста (например, «Январь», «Февраль», «Март» и т. Д.) В столбце E, вам придется приложить дополнительные усилия для создания дат, которые можно использовать в качестве критериев . Однако в этом случае мы используем простой трюк, чтобы упростить задачу: в столбце E вместо ввода названий месяцев мы добавляем фактические даты (01.01.2016, 01.02.2016, 01.03.2016), затем используйте настраиваемый формат даты («мммм») для отображения названий месяцев.
Это упрощает создание критериев, необходимых для СУММЕСЛИМН. Чтобы сопоставить даты, которые больше или равны первому числу месяца, мы используем:
">="&E4
И для сопоставления дат, меньших или равных последнему дню месяца, мы используем это:
"<="&EOMONTH(E4,0)
Если значение месяцев равно нулю, функция EOMONTH автоматически возвращает последний день месяца, указанный в дате.
Объединение с амперсандом (&) необходимо при построении критериев, в которых используется логический оператор с числовым значением.
Решение сводной таблицы
Сводная таблица - отличное решение, когда вам нужно суммировать данные по годам, месяцам, кварталам и так далее, потому что они сделают такую группировку за вас вообще без каких-либо формул. Для параллельного сравнения формул и сводных таблиц см. Это видео: Зачем нужны сводные таблицы.
