Как суммировать значения между двумя датами

Содержание
  1. Задача1 (1 текстовый критерий и 1 числовой)
  2. Задача2 (2 числовых критерия)
  3. Задача3 (2 критерия Дата)
  4. Задача4 (Месяц)
  5. Как работает функция СУММЕСЛИМН?
  6. Формула для суммирования значений между двумя датами
  7. Примеры использования функции СУММЕСЛИМН в Excel
  8. Динамический диапазон суммирования по условию в Excel
  9. СУММЕСЛИ в Excel примеры с логическими операторами (больше, меньше или равно)
  10. СУММЕСЛИ в Excel примеры с текстовым критерием
  11. СУММЕСЛИ в Excel примеры операторов сравнения со ссылками на ячейки
  12. Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ, суммирование исключая значение в ячейке F1
  13. СУММЕСЛИ примеры формул с подстановочными знаками
  14. Структура функции
  15. Примеры суммеслимн
  16. Постановка задачи
  17. Функция СУММЕСЛИМН, когда условий много
  18. Сумма, если Дата находится между двумя датами
  19. Сумма, если Дата больше, чем
  20. Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL

Задача1 (1 текстовый критерий и 1 числовой)

Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики ( ячейка D 2 ), у которых остаток ящиков на складе >=6 ( ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):

1. = СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;”>=”&E2)

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

  • B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
  • A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
  • D2 и “>=”&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.

Порядок аргументов различен в функциях СУММЕСЛИМН() и СУММЕСЛИ() . В СУММЕСЛИМН() аргумент интервал_суммирования является первым аргументом, а в СУММЕСЛИ() – третьим. При копировании и редактировании этих похожих функций необходимо следить за тем, чтобы аргументы были указаны в правильном порядке.

2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);–(B2:B13>=E2)) Разберем подробнее использование функции СУММПРОИЗВ() :

  • Результатом вычисления A2:A13=D2 является массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в Строке формул A2:A13=D2 , а затем нажав F9
  • Результатом вычисления B2:B13 является массив {3:5:11:98:4:8:56:2:4:6:10:11}, т.е. просто значения из столбца B
  • Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является {0:0:0:0:4:8:56:0:0:0:0:0}. При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
  • Разберем второе условие: Результатом вычисления –( B2:B13>=E2) является массив {0:0:1:1:0:1:1:0:0:1:1:1}. Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
  • Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .

4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) похожа на вышеупомянутую формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER

5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует предварительного создания таблицы с условиями (см. статью про функцию БДСУММ() ). Заголовки этой таблицы должны в точности совпадать с соответствующими заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И (см. диапазон D14:E15 ).

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Задача2 (2 числовых критерия)

Другой задачей может быть нахождение сумм ящиков только тех партий товаров, у которых количество ящиков попадает в определенный интервал, например от 5 до 20 (см. файл примера Лист 2Числа ).

Формулы строятся аналогично задаче 1: =СУММЕСЛИМН(B2:B13;B2:B13;”>=”&D2;B2:B13;”

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($B2>=$D$2;$B2

Задача3 (2 критерия Дата)

Другой задачей может быть нахождение суммарных продаж за период (см. файл примера Лист “2 Даты” ). Используем другую исходную таблицу со столбцами Дата продажи и Объем продаж .

Формулы строятся аналогично задаче 2: = СУММЕСЛИМН(B6:B17;A6:A17;”>=”&D6;A6:A17;”

Примечание . Даты могут быть представлены в числовой форме см. статью Как Excel хранит дату и время

При необходимости даты могут быть введены непосредственно в формулу = СУММЕСЛИМН(B6:B17;A6:A17;”>=15.01.2010″;A6:A17;”

Чтобы вывести условия отбора в текстовой строке используейте формулу =”Объем продаж за период с “&ТЕКСТ(D6;”дд.ММ.гг”)&” по “&ТЕКСТ(E6;”дд.ММ.гг”)

В последней формуле использован Пользовательский формат .

Задача4 (Месяц)

Немного модифицируем условие предыдущей задачи: найдем суммарные продаж за месяц(см. файл примера Лист Месяц ).

Формулы строятся аналогично задаче 3, но пользователь вводит не 2 даты, а название месяца (предполагается, что в таблице данные в рамках 1 года).

Месяц вводится с помощью Выпадающего списка , перечень месяцев формируется с использованием Динамического диапазона (для исключения лишних месяцев).

Как работает функция СУММЕСЛИМН?

Функция СУММЕСЛИМН в Excel используется для суммирования значений по нескольким критериям.

Синтаксис функции выглядит так:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

  • диапазон_суммирования – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
  • диапазон_условия1, условие1 – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
  • [диапазон_условия2], условие 2] – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;

Формула для суммирования значений между двумя датами

Итак, как я уже писал выше, у нас есть таблица с данными продаж по каждому дню. Наша задача посчитать сумму продаж за период с 1 июня 2018 по 15 июня 2018 года.

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

=СУММЕСЛИМН(B2:B28;A2:A28;”>=01.06.2018″;A2:A28;”<=15.06.2018″)

После ввода этой формулы, функция вернет значение 559 134₽. Это значение соответствует сумме продаж за период с 1 июня по 15 июня 2018 года.

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

Примеры использования функции СУММЕСЛИМН в Excel

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

Динамический диапазон суммирования по условию в Excel

Пример 1. В таблице содержатся данные о успеваемости студентов по некоторому предмету в университете. Определить итоговую оценку для студентов с фамилией, начинающейся на букву «А» при условии, что минимальный балл должен быть не менее 5 (успеваемость студентов оценивается по 10-бальной шкале).

Вид таблицы данных:

Для расчета суммарного балла согласно установленным критериям используем следующую формулу:

Описание аргументов:

  • C3:C14 – диапазон ячеек с баллами, из которых будут автоматически выбраны значения для расчета суммы, которые соответствуют установленным критериям;
  • C3:C14 – первый диапазон ячеек, к которому будет применен первый критерий;
  • “>5” – первое условие отбора значений из указанного выше диапазона;
  • B3:B14 – второй диапазон ячеек (с фамилиями студентов), к которому будет применен второй критерий;
  • “А*” – второе условие отбора значений (все фамилии, которые начинаются с буквы «А»).

Результат расчетов:

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

СУММЕСЛИ в Excel примеры с логическими операторами (больше, меньше или равно)

Давайте рассмотрим несколько примеров формул СУММЕСЛИ, которые вы можете использовать для суммирования значений для условий больше чем, меньше чем или равно заданному значению.

Примечание. Обратите внимание, что в формулах Excel СУММЕСЛИ оператор сравнения, за которым следует число или текст, всегда должен быть заключен в двойные кавычки (“”).

Критерий

Оператор

Пример формулы СУММЕСЛИ

Описание

Сумма, если больше

>

=СУММЕСЛИ(A2:A10; “>5”)

Суммирует значения больше 5 в ячейках A2:A10.

Сумма, если меньше

<

=СУММЕСЛИ(A2:A10; “<10”; B2:B10)

Суммирует значения в ячейках B2:B10, если соответствующее значение в столбце A меньше 10.

Сумма, если равно

=

(можно не указывать)

=СУММЕСЛИ(A2:A10; “=”&D1)

или

=СУММЕСЛИ(A2:A10;D1)

Суммирует значения в ячейках A2:A10, которые равны значению в ячейке D1.

Сумма, если не равно

<>

=СУММЕСЛИ(A2:A10; “<>”&D1; B2:B10)

Суммирует значения в ячейках B2:B10, если соответствующая ячейка в столбце A не равна значению в ячейке D1.

Сумма если больше или равно

>=

=СУММЕСЛИ(A2:A10; “>=5”)

Суммирует значения, которые больше или равны 5 в диапазоне A2:A10.

Сумма если меньше или равно

<=

=СУММЕСЛИ(A2:A10; “<=10”; B2:B10)

Суммирует значения в ячейках B2:B10, если соответствующее значение в столбце A меньше либо равно 10.

СУММЕСЛИ в Excel примеры с текстовым критерием

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

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

Критерий

Пример формулы СУММЕСЛИ

Описание

Сумма, если равно

Точное совпадение:

=СУММЕСЛИ(A2:A8; “бананы”; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит точное слово «бананы» и никакие другие слова или символы. Ячейки, содержащие «зеленые бананы», «бананы зеленые» или «бананы!» не будут считаться.

Частичное совпадение:

=СУММЕСЛИ(A2:A8; “*бананы*”; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит слово «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «зеленые бананы» или «бананы зеленые», будут учитываться для суммирования.

Сумма, если не равно

Точное совпадение:

=СУММЕСЛИ(A2:A8; “<>бананы”; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит любое значение, отличное от слова «бананы». Если ячейка содержит «бананы» вместе с некоторыми другими словами или символами, такими как «желтые бананы» или «бананы желтые», такие ячейки будут учитываться для суммирования.

Частичное совпадение:

=СУММЕСЛИ(A2:A8; “<>*бананы*”; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A не содержит слова «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «желтые бананы» или «бананы желтые», не суммируются.

Для получения дополнительной информации о частичном совпадении см. пункт СУММЕСЛИ примеры формул с подстановочными знаками.

А теперь, давайте посмотрим пример формулы «Сумма, если не равно» в действии. Как показано на изображении ниже, формула суммирует количество всех продуктов, кроме «Банана Дамский пальчик»:

=СУММЕСЛИ(A2:A8; “<>Банан Дамский пальчик”; C2:C8)

СУММЕСЛИ в Excel примеры операторов сравнения со ссылками на ячейки

Если вы хотите получить более универсальную формулу Excel СУММЕСЛИ, вы можете заменить числовое или текстовое значение в критериях ссылкой на ячейку, например:

= СУММЕСЛИ(A2:A8; “<>”&F1; C2:C8)

В этом случае вам не придется менять формулу СУММЕСЛИ, основанную на другом критерии – вы просто вводите новое значение в ссылочной ячейке.

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ, суммирование исключая значение в ячейке F1

Примечание. Когда вы используете логическое выражение с ссылкой на ячейку, вы должны использовать двойные кавычки (“”), чтобы начать текстовую строку и амперсанд (&), чтобы объединить и завершить строку, например «<>» и F1.

Оператор «равенства» (=) можно не использовать до ссылки на ячейку, поэтому обе приведенные ниже формулы эквивалентны и правильны:

Формула 1: =СУММЕСЛИ(A2:A8; “=” & F1; C2:C8)

Формула 2: =СУММЕСЛИ(A2:A8; F1; C2:C8)

СУММЕСЛИ примеры формул с подстановочными знаками

Если вы намерены условно суммировать ячейки на основе «текстовых» критериев и хотите суммировать путем частичного совпадения, вам нужно использовать подстановочные знаки в формуле СУММЕСЛИ.

Доступны следующие подстановочные знаки:

Звездочка (*) – представляет любое количество символов

Знак вопроса (?) – представляет один символ в определенном месте

Пример 1. Суммирование значений, основанные на частичном совпадении

Предположим, вы хотите суммировать количество, относящиеся ко всем видам бананов. Следующие формулы СУММЕСЛИ будут очень эффективны в таких случаях:

=СУММЕСЛИ(A2:A8; “*бананы*”;C2:C8) – критерий включает текст, заключенный в звездочки (*).

=СУММЕСЛИ(A2:A8; “*”&F1&”*”; C2:C8) – критерий включает ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для конкатенации строки.

Структура функции

Прежде чем рассматривать механизм работы оператора, необходимо ознакомится с содержанием функции, а также синтаксисом формулы. Суммеслимн расшифровывается как сумма данных, если выполняется множество.

Значения аргументов следующие:

  1. В диапазон суммирования записываете столбец или строку, по которой будет вычисляться сумма значений.
  2. Диапазон условия 1 нужен для задания массива, в котором будут применены параметры отбора данных.
  3. Условие 1 является дополнением к предыдущему аргументу. На этой позиции указывается критерий отбора в диапазоне условия 1.
  4. Затем идут дополнительные аргументы, с помощью которых можно уточнить параметры расчета суммы значений.

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

Примеры суммеслимн

Для начала создаете таблицу со значениями. Можно использовать различные форматы ячеек и в зависимости от этого будет меняться синтаксис внутри формулы. Для примера будем работать с таблицей следующего вида:

Как видите, ячейки содержат и даты, и текст, и цифры. Будем задавать критерии для поиска суммы, а также рассматривать структуру оператора под поставленную задачу.

  1. Примеры с датами продаж:

Задача: найти количество продуктов проданных 22.02.2019г.

В свободной ячейке пишете следующее: =СУММЕСЛИМН(С2:С8;B2:B8;B2)

  1. Примеры с двумя условиями:

Задача: определить сумму яблок, проданных 22.02.2019г.:

Функция будет выглядеть следующим образом: =СУММЕСЛИМН(C2:C8;B2:B8;B2;A2:A8;»Яблоко»)

где B2:B8;B2 – первое условие, а A2:A8;»Яблоко» – второе. Диапазон суммирования по столбцу Количество.

Важно! Все текстовые блоки внутри формулы записываются в двойных кавычках.

  1. Примеры с текстом и звездочкой:

Задача: найти количество яблок, проданных Артемом.

Содержимое пустой ячейки: =СУММЕСЛИМН(C2:C8;A2:A8;»Яблоко»;D2:D8;»А*»)

Использование подстановочного знака звездочка помогает искать продавца, имя которого начинается на букву А. При таком условии считается число яблок, проданных Артемом и Ангелиной, поскольку их имена начинаются на букву А. Чтобы найти именно продажи Артема, необходимо изменить аргумент на «Ар*».

  1. Также в числовых столбцах можно использовать операторы сравнения. Если в условие больше меньше или равно, то можно обозначить границы поиска по цифрам.

Задача: Найти дату продажи Ангелиной продуктов больше 10 единиц.

Функция имеет следующий вид: =СУММЕСЛИМН(B2:B8;D2:D8;»Ангелина»;C2:C8;»>10″)

Как видите, оператор СУММЕСЛИМН позволяет находить сумму значений исходя из нескольких параметров, выполнение которых обязательно. При этом нужно соблюдать правила одинаковости размера массивов и написания текстовых блоков. Единственным недостатком такой функции является то, что нельзя суммировать несколько диапазонов. Для этого лучше использовать СУММПРОИЗВ.

Постановка задачи

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина “Копейка”.

Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для “Копейки”), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) – в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3Условие3), и четвертую, и т.д. – при необходимости.

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

Сумма, если Дата находится между двумя датами

=СУММЕСЛИМН(сумма;начальная_дата;»>»&А1;конечная_дата;»<«&В1)

В суммировании значений, которые находятся между двумя датами, можно использовать функцию СУММЕСЛИМН.

В примере показано, ячейка H7 содержит формулу:

=СУММЕСЛИМН(D5:D11; C5:C11; «>15.07.2019»; C5:C11; «<15.08.2019»)

Эта формула суммирует суммы в столбце D, если Дата в столбце C между датой в Н5 и Н6. В примере, Н5 содержит 15 июля 2019 и H6 содержит 15 августа 2019.

Функция СУММЕСЛИМН поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), и несколько критериев.

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

«>»&Н5;Дата // критерий 1

«<«&Н6;Дата // критерий 2

Обратите внимание, что мы должны заключить логические операторы в двойные кавычки ( «» ), а затем присоединиться с ссылками на ячейки с помощью амперсанда (&).

Если вы хотите включить Дату начала или окончания, а также сроки между ними, используйте больше или равно («>=») и меньше или равно («<=»).

Сумма, если Дата больше, чем

В сумме, если дата превышает определенную дату, вы можете использовать функцию СУММЕСЛИ.

В примере показано, ячейка H4 содержит формулу:

=СУММЕСЛИ(C5:C11; «>1.10.2019»;D5:D11)

Эта формула суммирует суммы в столбце D, если Дата в столбце C больше 1 октября 2019 года.

Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.

В данном случае, мы хотим чтобы дата была больше, чем 1 октября 2019 года, поэтому мы используем оператор больше чем (>).

Обратите внимание, что мы должны поставить оператор «больше, чем» в двойные кавычки и присоединить к нему амперсанд (&).

Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL

​Смотрите также​ 0 соответственно и​ с учетом установленных​ отбора значений (все​ массива.​ 0*1*1 = 0​ функция БДСУММ извлекает​ от других слов,​ будет суммировать все​из поиска исключаются​

​Проданное количество​ превышает установленный уровень.​ в ячейке B1.​ 1048576 значений:​ 4-х задач является​D14:E15​ этих похожих функций​

Источники


  • https://excel2.ru/articles/funkciya-summeslimn-slozhenie-s-neskolkimi-kriteriyami-v-ms-excel-chast-2uslovie-i-summeslimn
  • https://excelhack.ru/kak-summirovat-znacheniya-mezhdu-dvumya-datami/
  • https://exceltable.com/funkcii-excel/primery-funkcii-summeslimn
  • https://naprimerax.org/posts/68/funktciia-summesli-v-excel
  • https://mir-tehnologiy.ru/summeslimn-v-excel-primery/
  • https://www.planetaexcel.ru/techniques/2/167/
  • https://excelpedia.ru/formuli-v-excel/summy-s-datami
  • https://my-excel.ru/excel/summesli-v-excel-po-dvum-kriterijam.html

Рейтинг
( Пока оценок нет )
Понравилась статья? Поделиться с друзьями:
Все об Экселе: формулы, полезные советы и решения