Суммировать диапазон дат - формула не видит даты

Question

Решено - ответ ниже. Здравствуйте. Нужно суммировать цены в заданном диапазоне дат. Использую Суммеслимн, но возвращается значение 0. Количество строк почти 2000. Может быть из-за этого? И как сделать формулу рабочей?

Answer 1

Здравствуйте АннаЛеска,

Спасибо за обновление.

Как насчет этих формул? Вы пробовали это?

Суммирование в динамическом диапазоне на основе текущей даты
В ситуации, когда вам нужно суммировать данные в динамическом диапазоне дат (X дней назад с сегодняшнего дня или Y дней вперед), постройте критерии с помощью функции TODAY, которая получит текущую дату и обновит ее автоматически.

Например, для суммирования бюджетов, которые должны быть выплачены за последние 7 дней, включая сегодняшнюю дату, формула выглядит следующим образом:

=СУММЕСЛИМН(B2:B10; C2:C10, "<="&TODAY(), C2:C10, ">"&TODAY()-7)

Если вы не хотите включать текущую дату в конечный результат, используйте оператор less than (<) для первого критерия, чтобы исключить сегодняшнюю дату, и больше или равно (>=) для второго критерия, чтобы включить дату, которая находится за 7 дней до сегодняшнего дня:

=SUMIFS(B2:B10; C2:C10, "<"&TODAY(), C2:C10, ">="&TODAY()-7)

Суммируйте значения в пределах диапазона на основе сегодняшней даты

Аналогичным образом можно суммировать значения, если дата — это заданное количество дней вперед.

Например, чтобы получить общее количество бюджетов, которые должны быть выплачены в ближайшие 3 дня, используйте одну из следующих формул:

Сегодняшняя дата включена в результат:

=SUMIFS(B2:B10, C2:C10, ">="&TODAY(), C2:C10, "<"&TODAY()+3) Сегодняшняя дата не включена в результат: =SUMIFS(B2:B10, C2:C10, ">"&TODAY(), C2:C10, "<="&TODAY()+3)

Суммируйте значения за заданное количество дней вперед с сегодняшнего дня

Сумма между двумя датами и другими критериями
Чтобы суммировать значения в диапазоне дат, которые соответствуют какому-либо другому условию в другом столбце, просто добавьте еще одну пару диапазон/условие в формулу СУММЕСЛИМН.

Например, чтобы суммировать бюджеты в пределах определенного диапазона дат для всех проектов, которые содержат «подсказку» в своих именах, расширьте формулу с помощью подстановочных критериев:

=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1, A2:A10, "tip*")

Где A2:A10 — названия проектов, B2:B10 — числа для суммирования, C2:C10 — даты для проверки, F1 — дата начала и G1 — дата окончания.

Конечно, ничто не мешает вам ввести третий критерий в отдельную ячейку и ссылаться на эту ячейку, как показано на скриншоте:
Формула для суммирования между двумя датами и другими критериями

Синтаксис критериев даты СУММЕСЛИМН
Когда дело доходит до использования дат в качестве критериев для функций Excel SUMIF и SUMIFS, вы не будете первым, кто запутается :)

Однако при ближайшем рассмотрении все разнообразие вариантов использования сводится к нескольким простым правилам:

Если вы ставите даты непосредственно в аргументы критериев, то введите логический оператор (>, <, =, <>) прямо перед датой и заключите весь критерий в кавычки. Например:

=SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "<=9/20/2020")

Когда дата вводится в предопределенную ячейку, предоставьте условия в виде текстовой строки: заключите логический оператор в кавычки, чтобы начать строку, и используйте амперсанд (&) для объединения и завершения строки. Например:

=SUMIFS(B2:B10; C2:C10, ">="&F1, C2:C10, "<="&G1)

Если дата управляется другой функцией, такой как DATE или TODAY(), объедините оператор сравнения и функцию. Например:

=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "<="&TODAY())

Excel SUMIFS между датами не работает
Если ваша формула не работает или дает неправильные результаты, следующие советы по устранению неполадок могут пролить свет на то, почему она не работает, и помочь вам устранить проблему.

Проверьте формат дат и чисел
Если кажущаяся правильной формула SUMIFS не возвращает ничего, кроме нуля, первое, что нужно проверить, это то, что ваши даты на самом деле являются датами, а не текстовыми строками, которые выглядят только как даты. Затем убедитесь, что вы суммируете числа, а не числа, хранящиеся в виде текста. Следующие учебники помогут вам обнаружить и исправить эти проблемы.

Как изменить "текстовые даты" на реальные даты
Как преобразовать текст в число
Использование правильного синтаксиса для критериев
При проверке дат с помощью SUMIFS в кавычках должна быть указана дата типа «>=10.09.2020»; ссылки на ячейки и функции должны быть размещены вне кавычек, таких как "<="&G1 или "<="&TODAY(). Для получения более подробной информации, пожалуйста, смотрите синтаксис критериев даты.

Проверка логики формулы
Небольшая опечатка в бюджете может стоить миллионы. Небольшая ошибка в формуле может стоить часов времени отладки. Так, при суммировании между 2 датами проверьте, предшествует ли дате начала оператор больше (>) или больше или равно (>=), а конечная дата имеет префикс меньше (<) или меньше или равно (<=).

Убедитесь, что все диапазоны имеют одинаковый размер
Для корректной работы функции СУММЕСЛИМН диапазон сумм и диапазоны условий должны быть одинакового размера, в противном случае #VALUE! возникает ошибка. Чтобы исправить это, убедитесь, что все criteria_range аргументы имеют такое же количество строк и столбцов, как и sum_range.

Надеюсь, эта информация поможет. Если у вас есть какие-либо вопросы, пожалуйста, дайте мне знать, и я буду рад помочь вам в дальнейшем.

Всего хорошего
Юнг

Этот ответ был автоматически переведен.В результате могут быть грамматические ошибки или некорректные выражения?

Answer 2

Решено. Кому интересно. Выделить столбец с датами (диапазон без заголовка). Данные - текст по столбцам - ничего не делать, сразу "готово". Формула видит даты.

Answer 3

Привет АннаЛекса,

Я Юнг один из независимых советников, и я был бы рад помочь вам с вашим вопросом.

Как суммировать, если между двумя датами в Excel
Чтобы суммировать значения в определенном диапазоне дат, используйте формулу СУММЕСЛИМН с начальными и конечными датами в качестве критериев. Синтаксис функции СУММЕСЛИМН требует, чтобы сначала были указаны значения для сложения (sum_range), а затем предоставлены пары диапазон/условие. В нашем случае диапазон (список дат) будет одинаковым для обоих критериев.

Учитывая вышесказанное, общие формулы для суммирования значений между двумя датами принимают следующую форму:

Включая пороговые даты:

СУММЕСЛИМН(sum_range, даты;">=start_date", даты, "<=end_date")
Исключая пороговые даты:

SUMIFS(sum_range, dates,">start_date", dates, "=) и меньше или равно (<=) для включения пороговых дат в результат. Вторая формула проверяет, является ли дата больше (>) или меньше (<), исключая даты начала и окончания. В приведенной ниже таблице предположим, что вы хотите суммировать проекты, которые должны быть выполнены в определенном диапазоне дат включительно. Чтобы это было сделано, используйте следующую формулу: =SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "<=9/20/2020")

Если вы не хотите жестко кодировать диапазон дат в формуле, то вы можете ввести дату начала в F1, дату окончания в G1, объединить логические операторы и ссылки на ячейки и заключить все критерии в кавычки следующим образом:

=SUMIFS(B2:B10; C2:C10, ">="&F1, C2:C10, "<="&G1)

Формула для суммирования данных между 2 датами

Чтобы избежать возможных ошибок, вы можете указать даты с помощью функции DATE:

=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "<="&DATE(2020,9,20))

Вы можете проверить эту ссылку для получения более подробной информации о формуле:

https://www.ablebits.com/office-addins-blog/2020/10/07/excel-sumifs-date-range-between-two-dates/

Примечание: Это веб-сайт стороннего производителя. Страница, по-видимому, предоставляет точную и безопасную информацию. Следите за рекламой на сайте, которая может рекламировать продукты, часто классифицируемые как PUP (потенциально нежелательные продукты). Тщательно изучите любой продукт, рекламируемый на сайте, прежде чем вы решите скачать и установить его.

Надеюсь, эта информация поможет. Если у вас есть какие-либо вопросы, пожалуйста, дайте мне знать, и я буду рад помочь вам в дальнейшем.

Всего хорошего
Юнг

Этот ответ был автоматически переведен.В результате могут быть грамматические ошибки или некорректные выражения?

Answer 4

Привет АннаЛекса,

Я был бы рад помочь вам с вашим запросом или проблемой.

Учитывая вышесказанное, общие формулы для суммирования значений между двумя датами принимают следующую форму:

Включая пороговые даты:

СУММЕСЛИМН(sum_range, даты;">=start_date", даты, "<=end_date")
Исключая пороговые даты:

SUMIFS(sum_range, dates,">start_date", dates, "=) и меньше или равно (<=) для включения пороговых дат в результат. Вторая формула проверяет, является ли дата больше (>) или меньше (<), исключая даты начала и окончания. В приведенной ниже таблице предположим, что вы хотите суммировать проекты, которые должны быть выполнены в определенном диапазоне дат включительно. Чтобы это было сделано, используйте следующую формулу: =SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "<=9/20/2020")

Если вы не хотите жестко кодировать диапазон дат в формуле, то вы можете ввести дату начала в F1, дату окончания в G1, объединить логические операторы и ссылки на ячейки и заключить все критерии в кавычки следующим образом:

=SUMIFS(B2:B10; C2:C10, ">="&F1, C2:C10, "<="&G1)

Формула для суммирования данных между 2 датами

Чтобы избежать возможных ошибок, вы можете указать даты с помощью функции DATE:

=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "<="&DATE(2020,9,20))

Примечание: Этот ответ был автоматически переведен. В результате могут возникнуть грамматические ошибки или странные выражения.

Я надеюсь, что эта информация поможет вам. Если у вас есть какие-либо вопросы, пожалуйста, дайте мне знать, и я буду более чем рад помочь вам в этом.

Всего хорошего

Мишель

Этот ответ был автоматически переведен.В результате могут быть грамматические ошибки или некорректные выражения?

Answer 5

Я именно так вводила эту формулу - не получается.... Сейчас стала кликать мышкой в ячейках с датой, а потом нажимаю enter и формула начинает видеть эти даты и суммировать нужные цены. Но строк очень много. Как ускорить этот процесс? Копирование форматов не помогло.