Загальна формула
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Резюме
Підсумовуючи за місяцями, ви можете використовувати формулу на основі функції SUMIFS за допомогою функції EOMONTH. У наведеному прикладі формула в F4:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Ця формула використовує названі діапазони "сума" (C4: C9) і "дата" (B4: B9).
Пояснення
Функція SUMIFS може підсумовувати значення в діапазонах на основі кількох критеріїв.
У цьому випадку ми налаштовуємо SUMIFS для підсумовування сум за місяцями, використовуючи два критерії: один для збігу дат, що перевищують або дорівнюють першому дню місяця, один - для дат, менших або рівних останньому дню місяця. Іншими словами, нам потрібна така формула:
=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 замість введення назв місяців ми додаємо фактичні дати (1/1/2016, 2/1/2016, 3/1/2016), потім використовуйте власний формат дати ("мммм") для відображення назв місяців.
Це полегшує побудову критеріїв, необхідних нам для SUMIFS. Для узгодження дат, більших або рівних першому числа місяця, ми використовуємо:
">="&E4
А для відповідності датам, меншим або рівним останньому дню місяця, ми використовуємо це:
"<="&EOMONTH(E4,0)
При нулі протягом місяців функція EOMONTH автоматично повертає останній день місяця, вказаний датою.
Об’єднання за допомогою амперсанда (&) необхідно при побудові критеріїв, що використовують логічний оператор із числовим значенням.
Рішення зведеної таблиці
Зведена таблиця - відмінне рішення, коли вам потрібно узагальнити дані за роками, місяцями, кварталами тощо, оскільки вони зроблять для вас такий тип групування, не маючи взагалі жодних формул. Порівняльне порівняння формул із зведеними таблицями див. У цьому відео: Навіщо зведені таблиці.
