Формула диапазона дат СУММЕСЛИМН в Excel
Работая над отчетом, инвестиционным планом или любым другим набором данных с датами, вам часто может понадобиться суммировать числа за определенный период. Этот учебник научит вас быстрому и простому решению — формуле СУММЕСЛИМН с диапазоном дат в качестве критерия.
В нашем блоге и на других форумах Excel люди часто спрашивают, как использовать СУММЕСЛИ для диапазона дат. Дело в том, что для суммирования между двумя датами вам нужно определить обе даты, в то время как функция СУММЕСЛИ в Excel допускает только одно условие. К счастью, у нас также есть функция СУММЕСЛИМН, которая поддерживает несколько критериев.
Как суммировать, если между двумя датами в Excel
Чтобы суммировать значения в пределах определенного диапазона дат, используйте формулу СУММЕСЛИМН с начальной и конечной датами в качестве критериев. Синтаксис функции СУММЕСЛИМН требует, чтобы вы сначала указали значения для сложения (диапазон_суммы), а затем предоставили пары диапазон/критерий. В нашем случае диапазон (список дат) будет одинаковым для обоих критериев.
Учитывая вышеизложенное, общие формулы для суммирования значений между двумя датами принимают следующий вид:
В том числе пороговые даты:
СУММЕСЛИМН(сумма_диапазон, даты,”>=Дата начала“, даты“<=Дата окончания“)
За исключением пороговых дат:
СУММЕСЛИМН(сумма_диапазон, даты,”>Дата начала“, даты“<Дата окончания“)
Как видите, разница только в логических операторах. В первой формуле используем лучше чем или же равно (>=) и меньше или равно (<=), чтобы включить в результат пороговые даты. Вторая формула проверяет, является ли дата лучше чем (>) или меньше, чем (<), опуская даты начала и окончания.
В приведенной ниже таблице предположим, что вы хотите суммировать проекты, которые должны быть выполнены в определенном диапазоне дат включительно. Чтобы это сделать, используйте эту формулу:
=СУММЕСЛИМН(B2:B10, C2:C10, “>=10.09.2020”, C2:C10, “<=20.09.2020")
Если вы не хотите жестко указывать диапазон дат в формуле, вы можете ввести дату начала в F1, дату окончания в G1, соединить логические операторы и ссылки на ячейки и заключить все критерии в кавычки следующим образом:
=СУММЕСЛИМН(B2:B10, C2:C10, “>=”&F1, C2:C10, “<="&G1)
Чтобы избежать возможных ошибок, вы можете указать даты с помощью функции DATE:
=СУММЕСЛИМН(B2:B10, C2:C10, “>=”&ДАТА(2020,9,10), C2:C10, “<="&ДАТА(2020,9,20))
Сумма в динамическом диапазоне на основе сегодняшней даты
В ситуации, когда вам нужно суммировать данные в динамическом диапазоне дат (X дней назад с сегодняшнего дня или Y дней вперед), создайте критерии с помощью функции СЕГОДНЯ, которая получит текущую дату и обновит ее автоматически.
Например, для суммирования бюджетов, подлежащих оплате за последние 7 дней, включая сегодняшнюю дату, используется следующая формула:
=СУММЕСЛИМН(B2:B10, C2:C10, “<="&СЕГОДНЯ(), C2:C10, ">“&СЕГОДНЯ()-7)
Если вы не хотите включать текущую дату в окончательный результат, используйте меньше, чем оператор (<) для первого критерия исключения сегодняшней даты и больше или равно (>=) для второго критерия, чтобы включить дату за 7 дней до сегодняшнего дня:
=СУММЕСЛИМН(B2:B10, C2:C10, “<"&СЕГОДНЯ(), C2:C10, ">=”&СЕГОДНЯ()-7)
Аналогичным образом вы можете суммировать значения, если дата является заданным количеством дней вперед.
Например, чтобы получить общую сумму бюджетов, подлежащих оплате в течение следующих 3 дней, используйте одну из следующих формул:
Сегодняшняя дата включена в результат:
=СУММЕСЛИМН(B2:B10, C2:C10, “>=”&СЕГОДНЯ(), C2:C10, “<"&СЕГОДНЯ()+3)
Сегодняшняя дата не включается в результат:
=СУММЕСЛИМН(B2:B10, C2:C10, “>”&СЕГОДНЯ(), C2:C10, “<="&СЕГОДНЯ()+3)
Сумма, если между двумя датами и другим критерием
Чтобы суммировать значения в пределах диапазона дат, которые соответствуют другому условию в другом столбце, просто добавьте еще одну пару диапазон/критерий в формулу СУММЕСЛИМН.
Например, чтобы просуммировать бюджеты в определенном диапазоне дат для всех проектов, в названиях которых есть слово «совет», дополните формулу подстановочным знаком:
=СУММЕСЛИМН(B2:B10, C2:C10, “>=”&F1, C2:C10, “<="&G1, A2:A10, "подсказка*")
Где A2:A10 — названия проектов, B2:B10 — числа для суммирования, C2:C10 — даты для проверки, F1 — дата начала и G1 — дата окончания.
Конечно, ничто не мешает вам также ввести третий критерий в отдельную ячейку и сослаться на эту ячейку, как показано на скриншоте:
Синтаксис критериев даты СУММЕСЛИМН
Когда дело доходит до использования дат в качестве критериев для функций Excel СУММЕСЛИ и СУММЕСЛИМН, вы не будете первым, кто запутается 🙂
Однако при ближайшем рассмотрении все многообразие вариантов использования сводится к нескольким простым правилам:
Если вы помещаете даты непосредственно в аргументы критериев, то введите логический оператор (>, <, =, <>) прямо перед датой и заключите все критерии в кавычки. Например:
=СУММЕСЛИМН(B2:B10, C2:C10, “>=10.09.2020”, C2:C10, “<=20.09.2020")
Когда дата вводится в предопределенную ячейку, предоставьте критерии в виде текстовой строки: заключите логический оператор в кавычки, чтобы начать строку, и используйте амперсанд (&), чтобы объединить и завершить строку. Например:
=СУММЕСЛИМН(B2:B10, C2:C10, “>=”&F1, C2:C10, “<="&G1)
Когда дата управляется другой функцией, такой как DATE или TODAY(), объедините оператор сравнения и функцию. Например:
=СУММЕСЛИМН(B2:B10, C2:C10, “>=”&ДАТА(2020,9,10), C2:C10, “<="&СЕГОДНЯ())
Excel SUMIFS между датами не работает
Если ваша формула не работает или дает неправильные результаты, следующие советы по устранению неполадок могут пролить свет на то, почему она не работает, и помочь вам устранить проблему.
Проверьте формат дат и чисел
Если, казалось бы, правильная формула СУММЕСЛИМН возвращает только ноль, первое, что нужно проверить, это то, что ваши даты действительно являются датами, а не текстовыми строками, которые только выглядят как даты. Затем убедитесь, что вы суммируете числа, а не числа, сохраненные в виде текста. Следующие руководства помогут вам выявить и устранить эти проблемы.
Используйте правильный синтаксис для критериев
При проверке дат с помощью СУММЕСЛИМН дату следует заключать в кавычки, например «>=9/10/2020»; ссылки на ячейки и функции должны быть помещены вне кавычек, например “<="&G1 или "<="&TODAY()". Для получения полной информации см. Синтаксис критериев даты.
Проверьте логику формулы
Небольшая опечатка в бюджете может стоить миллионы. Небольшая ошибка в формуле может стоить часов времени на отладку. Итак, при суммировании между двумя датами проверьте, предшествует ли дате начала лучше чем (>) или больше или равно (>=), а перед датой окончания ставится префикс меньше, чем (<) или меньше или равно (<=).
Убедитесь, что все диапазоны имеют одинаковый размер
Для правильной работы функции СУММЕСЛИМН диапазон суммы и диапазон критериев должны быть одинакового размера, в противном случае #ЗНАЧ! возникает ошибка. Чтобы исправить это, убедитесь, что все критерии_диапазон аргументы имеют то же количество строк и столбцов, что и сумма_диапазон.
Вот как можно использовать функцию СУММЕСЛИМН в Excel для суммирования данных в диапазоне дат. Если у вас есть какие-то другие интересные решения, я буду очень признателен, если вы поделитесь ими в комментариях. Спасибо за внимание и надеемся увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Примеры диапазонов дат СУММЕСЛИМН (файл .xlsx)
