Общая формула
=SUMPRODUCT(--(MONTH(birthday)=number))
Резюме
Чтобы подсчитать количество дней рождения в списке, вы можете использовать формулу, основанную на функциях СУММПРОИЗВ и МЕСЯЦ. В показанном примере E5 содержит эту формулу:
=SUMPRODUCT(--(MONTH(birthday)=D5))
Эта формула считает дни рождения в январе (поскольку D5 содержит 1) в именованном диапазоне «дни рождения» (B5: B104).
Объяснение
Вы могли подумать, что можете использовать функцию СЧЁТЕСЛИ для подсчета дней рождения, но проблема в том, что СЧЁТЕСЛИ работает только с диапазонами и не позволит вам использовать что-то вроде МЕСЯЦА для извлечения только номера месяца из дат. Итак, вместо этого мы используем СУММПРОИЗВ.
Внутри SUMPRODUCT у нас есть это выражение:
MONTH(birthday)=D5)
Функция МЕСЯЦ извлекает месяц для каждой даты в названном диапазоне "дни рождения", и это сравнивается со значением в D5, равным 1. Результатом является массив значений ИСТИНА / ЛОЖЬ, где каждое ИСТИНА представляет дату, где месяц = 1.
Затем значения TRUE FALSE преобразуются в единицы и нули с двойным отрицательным знаком (-). СУММПРОИЗВ суммирует эти числа и возвращает окончательный результат.
Работа с пустыми ячейками
Если у вас есть пустые ячейки в списке дней рождения, вы получите неверные результаты, так как МЕСЯЦ (0) возвращает 1. Для обработки пустых ячеек вы можете настроить формулу следующим образом:
=SUMPRODUCT((MONTH(birthdays)=D5)*(birthdays""))
Умножение на выражение (дни рождения "") эффективно отменяет значения месяца для пустых ячеек. См. Страницу SUMPRODUCT для получения дополнительной информации о том, как логические выражения работают внутри SUMPRODUCT.
Решение для сводной таблицы
Сводная таблица также является отличным решением этой проблемы.
