• range (диапазон) – диапазон ячеек, по которым оцениваются критерии. Аргументом могут быть числа, текст, массивы или ссылки, содержащие числа;
  • criteria(условие) – критерии, которые проверяются по указанному диапазону ячеек и определяют, какие ячейки суммировать;
  • sum_range(диапазон_суммирования) – (не обязательно) суммируемые ячейки. Если этот аргумент не указан, то функция использует аргумент range (диапазон)в качестве sum_range (диапазон_суммирования).
  • суммирование ячеек в функции СУММЕСЛИ производится на основе одного критерия, если вам необходимо указать несколько критериев – используйте функцию SUMIFS (СУММЕСЛИМН) ;
  • пустые и текстовые ячейки в аргументе sum_range (диапазон_суммирования) игнорируются;
  • критерием для функции могут служить числа, выражения, результаты вычисления из других ячеек, текст или формула;
  • если в качестве критерия указан текст или математический/логический символ (=,+,-,/,*), то такие значения должны быть указаны в двойных кавычках;
  • аргумент с критерием не должен быть длиннее чем 255 символов;
  • в качестве критерия могут использоваться подстановочные знаки (*);
  • если количество ячеек в диапазоне критерия и аргументе sum_range (диапазон_суммирования) различаются, то размер диапазона критерия будет в приоритете.

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

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

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

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

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

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

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

Пример 1. В таблице Excel записаны члены геометрической прогрессии. Какую часть (в процентах) составляет сумма первых 15 членов прогрессии от общей суммы ее членов.

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

Выполним расчет с помощью следующей формулы:

  • A3:A22 – диапазон ячеек, содержащих порядковые номера членов прогрессии, относительно которых задается критерий суммирования;
  • " Пример 2. В таблице Excel указаны данные о работе двух продавцов небольшого магазина. Определить, какой из сотрудника принес больший доход за 19 рабочих дней).

Исходная таблица имеет следующий вид:

Для расчета используем функцию в формуле:

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

В итоге получим следующее значение:

Функция СУММ в Excel

В программе Excel используются массивы, или данные, которые объединены в группы. Ими являются таблицы с различными значениями. Чтобы узнать сумму целого массива или нескольких больших массивов, используется функция «СУММ»:

    Выделите ту ячейку, в которую планируется вставить итоговое значение сложения. Затем кликните по кнопке «Вставить функцию», которая расположена рядом со строкой для формул.

Функция СУММ в Excel

Функция СУММ в Excel

Функция СУММ в Excel

В выделенной ячейке появится сумма выбранного массива, а в строке ввода – функция. Если нужно добавить еще один диапазон данных, то для этого необходимо в окне «Аргументы функции» в строке «Число 2» его определить, затем нажать на «ОК». В выбранной ячейке к ранее полученному результату прибавятся новые данные.

Функция СУММ в Excel

Программа Excel позволяет прописать функцию самостоятельно без использования дополнительных диалоговых окон. Чтобы это сделать, выберите нужную пустую ячейку и в строке формул вручную пропишите функцию с диапазоном. Например, =СУММ(B5:B7) , чтобы посчитать итог продаж за четверг, пятницу и субботу.

Функция СУММ в Excel

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

СУММ с помощью горячих клавиш

Управление функциями в Excel может осуществляться с помощью горячих клавиш, которые введут функцию в выбранную ячейку автоматически. Чтобы вставить функцию =СУММ , действуйте следующим образом:

  1. Выберите пустую ячейку, в которую будет вписана сумма.
  2. Нажмите и удерживайте клавишу Alt , затем один раз нажмите на клавишу «равно» = .
  3. Отпустите Alt . В выделенную ячейку будет вставлена функция СУММ .
  4. Нажмите Enter , чтобы Excel подсчитал выбранный диапазон.

Данное сочетание клавиш работает на Windows. Для операционной системы MacOS функция активируется одновременным нажатием клавиш Shift + Cmd + T .

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

Примечания

Функция СУММЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов или применяется к строке #ЗНАЧ!.

Аргумент диапазон_суммирования может не совпадать по размерам с аргументом диапазон. При определении фактических ячеек, подлежащих суммированию, в качестве начальной используется верхняя левая ячейка аргумента диапазон_суммирования, а затем суммируются ячейки части диапазона, соответствующей по размерам аргументу диапазон. Пример:

Excel SUMIF функция

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

Синтаксис:

Синтаксис функции СУММЕСЛИ в Excel:

Аргументы:

  • range : Необходимые. Диапазон ячеек, к которым вы хотите применить критерии.
  • criteria : Необходимые. Критерии, используемые для определения, какие ячейки необходимо добавить. Критерий может быть текстом, числом, датой, логическим выражением, ссылкой на ячейку или другой функцией.
  • Внимание: Любые текстовые критерии или критерии, содержащие математические символы, должны быть заключены в двойные кавычки. Если критерий числовой, двойные кавычки не требуются.
  • sum_range :Необязательный. Это диапазон ячеек для суммирования. Если этот параметр опущен, в качестве диапазон_суммы.

Вернуть:

Возврат суммирования для списка чисел на основе определенных критериев.

Примеры:

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

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

Предположим, у вас есть таблица, которая содержит столбцы Name, Qty и Price, как показано на скриншоте ниже, как вы можете получить общую цену, если Qty больше 250?

Пожалуйста, примените приведенную ниже формулу в пустую ячейку:

  • Советы: В приведенной выше формуле:
  • B2: B8: диапазон ячеек, в которых будет выполняться поиск критериев.
  • > 250“: это условие, по которому вы хотите суммировать значения ячеек.
  • C2: C8: относится к суммируемому диапазону.

Затем нажмите Enter ключ, чтобы получить общую цену, если количество превышает 250, как показано на следующем снимке экрана:

Внимание: Вы можете использовать другие операторы сравнения, например, больше или равно (> =), меньше (<), меньше или равно (<=), равно (=), не равно (<>), чтобы ваша потребность.

Пример 2: формулы СУММЕСЛИ с текстовыми критериями

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

Случай 1. Суммируйте значения ячеек, если они содержат определенный текст в другом столбце.

В качестве примера возьмите данные ниже, чтобы получить все общее количество, если имя Дэвид в столбце A.

Введите или скопируйте приведенную ниже формулу в пустую ячейку:

  • Советы: В приведенной выше формуле:
  • A2: A8: диапазон ячеек, в которых будет выполняться поиск критериев.
  • «Давид»: это условие, по которому вы хотите суммировать значения ячеек.
  • B2: B8: диапазон суммируемых ячеек.

Внимание: Вы также можете использовать ссылку на ячейку, чтобы заменить условие в приведенной выше формуле следующим образом: =SUMIF(A2:A8, D2, B2:B8) .

Случай 2: значения ячеек суммы не равны определенному значению

Если вы хотите суммировать ячейки, когда ячейки не равны определенному значению, например, чтобы просуммировать все количество людей, кроме Дэвида, как вы могли бы это сделать?

Пожалуйста, примените приведенную ниже формулу в пустую ячейку:

  • Советы: В приведенной выше формуле:
  • A2: A8: диапазон ячеек, в которых будет выполняться поиск критериев.
  • «<> Дэвид»: это условие, по которому вы хотите суммировать значения ячеек.
  • B2: B8: диапазон суммируемых ячеек.

Внимание: Вы также можете использовать ссылку на ячейку, чтобы заменить условие в приведенной выше формуле следующим образом: =SUMIF(A2:A8,”<>”&D2, B2:B8) , вы должны использовать двойные кавычки (“”), чтобы заключить операторы сравнения, и использовать амперсанд (&), чтобы объединить ссылку на ячейку. Смотрите скриншот:

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

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

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

Вопросительный знак (?): Представляет любой отдельный символ в определенной позиции

Случай 1: Сумма значений на основе частичного совпадения с символом звездочки (*)

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

Здесь введите следующую формулу в пустую ячейку:

Затем нажмите Enter ключ, чтобы получить результат ниже:

Заметки:

1. Если вы хотите суммировать значения ячеек, которые начинаются с Kutools, используйте эту формулу: = СУММЕСЛИ (A2: A8; «Kutools *»; B2: B8) .

2. Чтобы суммировать значения ячеек, если соответствующая ячейка в столбце A заканчивается текстом Kutools, используйте эту формулу: = СУММЕСЛИ (A2: A8; «* Kutools»; B2: B8) .

3. Вы также можете использовать ссылку на ячейку, чтобы заменить условие в приведенной выше формуле следующим образом: =SUMIF(A2:A8, “*”&D2&”*”, B2:B8) . Вы должны использовать двойные кавычки (“”), чтобы заключить звездочку, и использовать амперсанд (&), чтобы объединить ссылку на ячейку. Смотрите скриншот:

Случай 2. Суммируйте значения с определенным количеством символов с вопросительным знаком (?)

Если вы хотите суммировать значения ячеек в списке, соответствующая ячейка содержит ровно 5 символов, как показано на следующем снимке экрана. Как бы вы могли решить эту задачу в Excel?

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

Чаевые: В этой формуле: один вопросительный знак обозначает один единственный символ, пять вопросительных знаков обозначают 5 символов.

А затем нажмите Enter key, вы получите нужный результат:

Пример 4: Суммирование значений ячеек, соответствующих пустым или непустым ячейкам

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

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

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

Читайте также:  Примеры формулы ВПР для автозаполнения реквизитов в Excel

Затем нажмите Enter ключ, чтобы получить результат общей цены, если в столбце A есть пустые ячейки, см. снимок экрана:

Случай 2: Суммирование значений ячеек, если соседняя ячейка не пустая.

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

А затем нажмите Enter key, все значения, в которых соседние ячейки содержат значения, были суммированы, см. снимок экрана:

Пример 5: СУММЕСЛИ с датами в Excel

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

Случай 1. Сумма значений, если дата является текущей датой или меньше, больше сегодняшней.

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

Затем нажмите Enter key, суммируются все ячейки, соответствующие сегодняшней дате. Смотрите скриншот:

Заметки:

1. Чтобы суммировать значения, если соответствующая дата меньше или раньше сегодняшней, используйте эту формулу: = СУММЕСЛИ (B2: B8; “<” & СЕГОДНЯ (); C2: C8) .

2. Чтобы суммировать значения, если соответствующая дата больше или позже сегодняшней, используйте эту формулу: = СУММЕСЛИ (B2: B8; “>” & СЕГОДНЯ (); C2: C8) .

Случай 2: сумма значений между двумя заданными датами

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

Пожалуйста, примените следующую формулу:

А затем нажмите клавишу Enter, было вычислено общее значение между двумя заданными датами, см. Снимок экрана:

Внимание: Вы также можете использовать ссылку на ячейку, чтобы заменить условие в приведенной выше формуле следующим образом: = СУММЕСЛИ (B2: B8, “> =” & E2, C2: C8) – СУММЕСЛИ (B2: B8, “> margin-left: 13px;”>

Пример 6: Сумма значений в нескольких столбцах в Excel

Эта функция СУММЕСЛИ может также помочь вам суммировать значения в нескольких столбцах на основе одного условия. Например, если у вас есть диапазон данных, как показано на скриншоте ниже, то теперь, чтобы получить общие значения продукта KTE за четыре месяца – январь, февраль, март и апрель. Как вы могли это сделать?

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

Пожалуйста, примените приведенную ниже формулу в пустую ячейку:

Затем нажмите клавишу Enter, чтобы получить правильный результат, который вам нужен, см. Снимок экрана:

Внимание: Вышеупомянутая функция СУММЕСЛИ несколько затруднительна для использования, если необходимо суммировать много столбцов, поэтому здесь простая функция СУММПРОИЗВ также может помочь вам справиться с этой задачей. Пожалуйста, используйте эту формулу: =SUMPRODUCT((A2:A8=G2)*(B2:E8)) , а затем нажмите Enter ключ для получения результата:

  • Советы: В приведенной выше формуле:
  • A2: A8: диапазон ячеек, в которых будет выполняться поиск критериев.
  • G2: это условие, по которому вы хотите суммировать значения ячеек.
  • B2: E8: диапазон суммируемых ячеек.

Другие статьи о функции СУММЕСЛИ:

  • Сумма соответствующих значений с той же датой в Excel
  • Вы когда-нибудь пытались суммировать соответствующие значения на основе той же даты из другого столбца в Excel? Предположим, у меня есть следующий диапазон данных (A1: B13), столбец A – это диапазон дат, содержащий несколько повторяющихся дат, а столбец B имеет номер заказа, теперь мне нужно сложить числа на основе той же даты в столбце A. Как быстро и легко закончить в Excel?
  • Ячейки Sumif не равны определенному значению в Excel
  • В этой статье я расскажу о том, как суммировать ячейки, если ячейки в другом столбце не равны определенному значению на листе Excel.
  • Дата Sumif меньше / больше сегодняшней в Excel
  • Предположим, у нас есть таблица объемов продаж с плановыми и фактическими объемами на каждый день. Теперь нам необходимо подсчитать даты, произошедшие до сегодняшнего дня, и вычислить общие объемы планирования до сегодняшнего дня, чтобы динамически сравнивать объемы планирования и фактические объемы. Здесь я применю функцию СУММЕСЛИ, чтобы быстро сделать это в Excel.
  • Сумиф с одним или несколькими критериями в Excel
  • В Excel суммирование значений на основе одного или нескольких критериев – обычная задача для большинства из нас, функция СУММЕСЛИ может помочь нам быстро суммировать значения на основе одного условия, а функция СУММЕСЛИМН помогает нам суммировать значения с несколькими критериями. В этой статье я опишу, как суммировать по одному или нескольким критериям в Excel?
  • Sumif на основе частичного совпадения в Excel
  • В качестве примера возьмем данные ниже. Я хочу просуммировать значения ячеек в столбце B, если в столбце A содержится частичный текст «KTE». Как этого добиться? В этой статье я расскажу о том, как суммировать значения ячеек на основе частичного совпадения в листе Excel.

Лучшие инструменты для работы в офисе

Kutools for Excel – поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

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

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

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия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 является массив , т.е. просто значения из столбца B ;
  • Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является . При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
  • Разберем второе условие: Результатом вычисления –( B2:B13>=E2) является массив . Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=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)

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

Этот пример можно считать классическим. Пусть есть таблица с данными о продажах некоторых товаров.

Данные о продажах

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

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

Диалоговое окно функции СУММЕСЛИ

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

Теперь давайте рассмотрим, пример, когда критерий не совпадает с диапазоном суммирования. Такая ситуация встречается гораздо чаще. Рассмотрим те же условные данные. Пусть нам нужно узнать сумму не больше или меньше какого-то значения, а сумму конкретной группы товаров, допустим, группы Г.

Для этого снова выделяем ячейку с будущим подсчетом суммы и вызываем Мастер функций. В первом окошке указываем диапазон, где содержится критерий, в нашем случае это столбец с названиями групп товаров. Далее сам критерий прописываем либо вручную, оставив в соответствующем поле запись «группа Г», либо просто указываем мышкой ячейку с нужным критерием. Последнее окошко – это диапазон, где находятся суммируемые данные.

Читайте также:  Сценарии в Excel позволяют прогнозировать результат

Диалоговое окно функции СУММЕСЛИ с отдельным критерием

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

Таблица для подтягивания данных по СУММЕСЛИ

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

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

Заполненное диалоговое окно функции СУММЕСЛИ

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

Подтянутые данные по СУММЕСЛИ

Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.

  • sum_range (диапазон_суммирования) – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
  • criteria_range1 (диапазон_условия1), сriteria1 (условие1) – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
  • [criteria_range2], criteria2([диапазон_условия2], условие 2) – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;
  • Суммирование значений в функции производится на основе нескольких критериев;
  • Пустые и текстовые ячейки игнорируются в аргументе sum_range (диапазон_суммирования);
  • Критерием могут служить числа, выражения, ссылка на ячейку, текст или формула;
  • Критерий, указанный в текстовом или логическом/математическом формате (=,+,-,/,*) должны быть указаны в двойных кавычках;
  • В качестве критерия могут использоваться подстановочные знаки (*);
  • Критерий не может быть больше чем 255 символов по длине;
  • Ячейки в аргументе sum_range (диапазон_суммирования) суммируются только при выполнении всех условий, заданных в формуле;
  • Если среди значений аргумента sum_range (диапазон_суммирования) указано логическое значение TRUE или FALSE, то они подразумевают значения “1” и “0” соответственно;
  • Диапазон аргументов sum_range (диапазон_суммирования) и всех критериев должны совпадать.

Теперь немного усложним задачу. В предыдущем кейсе была рассмотрена ситуация, когда данные оценивались в общих суммах за период времени в целом. Углубимся в данные и оценим их более детально.

Имеем те же вводные данные. Произведем оценку продаж каждого менеджера в динамике.

  • Добавляем поле “Месяц” в заголовок таблицы
  • Ставим курсор в ячейку “D2”, открываем “Мастер функций” и находим в категории “Дата и время” функцию “Месяц”
  • В диалоговом окне вводим положение ячейки “А2” нажав на нее мышкой

  • Нажимаем “ОК” и протягиваем до конца таблицы

Группируем данные по месяцам:

2.1. На соседнем листе формируем уникальный список значений столбца “Менеджеры”. Делаем это через инструмент “Удалить дубликаты” как в предыдущем примере.

2.2. Формируем таблицу с заголовками как на рисунке ниже:

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

В этом случае нам пригодится функция “СУММЕСЛИМН”

Посмотри справку о функциях из категории “Текстовые” – они достаточно простые для понимания и использования.

Обратить внимание следует на функции ПРАВСИМВ, ЛЕВСИМВ, ДЛСТР, СЦЕПИТЬ, ПСТР , о ни полезны во многих случаях обработки данных.
Часть из них мы будем использовать в дальнейших кейсах.

В критерии применяются подстановочные знаки (*, ?)

Просуммируем значения из столбца Количество, для которых соответствующее значение из столбца Фрукты содержит слово Яблоки (без учета РЕгиСТРА).

Для решения этой задачи используем подстановочные знаки (*, ?). Подход заключается в том, что для отбора текстовых значений в качестве критерия задается лишь часть текстовой строки. Например, для отбора всех ячеек, содержащих слова яблоки (свежие яблоки, яблоки местные и пр.) можно использовать критерии с подстановочным знаком * (звездочка). Для этого нужно использовать конструкцию *яблоки*.

Решение задачи выглядит следующим образом (учитываются значения содержащие слово яблоки в любом месте в диапазоне поиска):
=СУММЕСЛИ($A$3:$A$13;"*яблоки*";B3)

Альтернативный вариант без использования подстановочных знаков выглядит более сложно:
=СУММПРОИЗВ(B3:B13*НЕ(ЕОШ(ПОИСК("яблоки";A3:A13))))

Примеры, приведенные ниже, иллюстрируют другие применения подстановочных знаков.

Задача. Просуммировать значения, если соответствующие ячейки:

Задача

Критерий

Формула

Результат

Примечание

заканчиваются на слово яблоки, например, Свежие яблоки

11

Использован подстановочный знак * (перед значением)

начинаются на слово яблоки, например, яблоки местные

20

Использован подстановочный знак * (после значения)

начинаются с гру и содержат ровно 6 букв

56

Использован подстановочный знак ?

Пример функции СУММПРОИЗВ для суммы диапазона по нескольким условиям

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

=E2)*(B2:B12))’ >

Как видно на рисунке, мы получили аналогичный результат вычисления. Как работает СУММПРОИЗВ в данной формуле детально описано в статье: Формулы суммирования по нескольким условиям в Excel.

Данные вычисления основаны на умножении логических значений и числовых в столбце B2:B12. Результаты вычислений в первых двух логических выражений возвращают логические значения ИСТИНА=1 и ЛОЖЬ=0. Если хоть один из множителей будет равен нулю, то и результат = 0. Например, как читает данная формула первую строку:

Если же оба логические выражения возвращают значение ИСТИНА, тогда текущая ячейка столбца B с показателем продаж умножается на единицы, а затем суммируются все найденные значения. Для наглядного примера сформируем таблицу как видит формула СУММПРОИЗВ текущую ситуацию:

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

В предыдущей статье мы рассмотрели синтаксис функции СУММЕСЛИ в Excel, теперь давайте закрепим знания на практике при помощи ряда примеров формулы СУММЕСЛИ:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с проверкой на неравенство

Примечание . Как и большинство других функций Excel, СУММЕСЛИ нечувствительна к регистру, что означает, что «<> бананы», «<> Бананы» и «<> БАНАНЫ» будут давать точно такой же результат.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Использование функции СУММЕСЛИМН в Excel ее особенности примеры

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

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

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

Если вы хотите считать только те ячейки, которые начинаются или заканчиваются определенным текстом, добавьте только один * до или после текста:

=СУММЕСЛИ(A2:A8; "бананы*"; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A начинается со слова «бананы».

=СУММЕСЛИ(A2:A8; "*бананы"; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A заканчивается словом «бананы».

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

Пример 2. Суммирование по заданному количеству символов

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

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с условием суммирования, если длина текстовой строки в шесть букв

Пример 3. Сумма ячеек, соответствующих текстовым значениям

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

=СУММЕСЛИ(A2:A8; "?*"; C2:C8) – суммирует значения из ячеек C2:C8, если соответствующая ячейка в столбце A содержит не менее 1 символа.

=СУММЕСЛИ(A2:A8; "*"; C2:C8) – учитывает пустые ячейки, содержащие строки нулевой длины, возвращаемые некоторыми другими формулами, например ="".

Обе приведенные выше формулы игнорируют нетекстовые значения, такие как ошибки, логические значения, числа и даты.

Пример 4. Использование * или ? как обычные символы

Если вы хотите использовать либо *, либо ? для обработки в функции СУММЕСЛИ как литерала, а не подстановочного знака, то используйте перед этим знаком тильду (

). Например, следующая формула СУММЕСЛИ просуммирует значения в ячейках C2:C8, если ячейка в столбце A в той же строке содержит знак вопроса:

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

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

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

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

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

Сумма по определенной дате

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B равна 29.10.2017.

Сумма, если дата больше либо равна заданной в формуле дате

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B больше или равна 29.10.2017.

Сумма, если дата больше даты, указанной в ячейке

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B больше даты, указанной в ячейке F1.

Если вы хотите суммировать значения на основе текущей даты, вам необходимо использовать СУММЕСЛИ в сочетании с функцией СЕГОДНЯ(), как показано ниже:

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

Суммирование значений, за текущую дату

=СУММЕСЛИ(B2:B9; СЕГОДНЯ (); C2:C9)

Суммирование значений, меньше текущей даты, то есть до сегодняшнего дня.

Суммирование значений за неделю от текущей даты. (т.е. сегодня + 7 дней).

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

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

СУММЕСЛИ в заданном диапазоне дат

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

Эта формула суммирует значения в ячейках C2:C9, если дата в столбце B находится между 1 ноября 2017 года и 30 ноября 2017, включительно.

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ дата в диапазоне

Эта формула может показаться немного сложной с первого взгляда, но при более близком рассмотрении это выглядит довольно просто. Первая функция СУММЕСЛИ объединяет все ячейки в C2:C9, где соответствующая ячейка в столбце B больше или равна дате начала (в данном примере 1 ноября). Затем вам просто нужно вычесть значения, которые попадают после даты окончания (30 ноября), с помощью второй функции СУММЕСЛИ.

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

Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазон, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;"> 5")

Это видео — часть учебного курса Сложение чисел в Excel.

При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; "Иван"; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны "Иван".

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

Как использовать СУММЕСЛИ – Примеры.

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

СУММЕСЛИ больше, меньше или равно

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

Функция СУММЕСЛИ excel с примерами

Поясним примеры из скриншота выше:

  • Пример1. Складывает значения больше 6 в диапазоне ячеек A5: A15.
  • Пример2. Складывает значения в диапазоне ячеек B5: B15, если значение соответствующего столбца A меньше 8.
  • Пример3. Складывает значения в ячейках A5: A15, которые равны значению в ячейке С4. Два варианта написания
  • Пример4. Складывает значения в ячейках B5: B15, если соответствующая ячейка в столбце A отличается от значения в ячейке С4.
  • Пример5. Складывает значения больше или равные 8 в диапазоне A5: A15.
  • Пример6. Складывает значения в ячейках B5: B15, если соответствующее значение в столбце A меньше или равно 8.

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

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

Функция СУММЕСЛИ excel с примерами

Поясним примеры из скриншота выше:

  • Пример1. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит в точности слово «вишня» и никаких других слов или символов. Ячейки, содержащие «черешня» и «яблоки», не будут включены.
  • Пример2. Складывает значения ячеек В2:В6, если соответствующая ячейка в столбце A содержит слово «шня» отдельно или в сочетании с любым другим словом или символом. Ячейки, содержащие «черешня» и «вишня», будут добавлены т.к содержат в себе «шня».
  • Пример3. Складывает значения ячеек В2: В6, если соответствующая ячейка в столбце A содержит любое значение, кроме «Вишня».
  • Пример4. Складывает значения ячеек В2: В6, если соответствующая ячейка в столбце A не содержит слова «шня» ни отдельно, ни в сочетании с любым другим словом. В данном случае будет выведена только сумма яблок.

Как и большинство других функций Excel, СУММЕСЛИ не учитывает регистр, что означает, что «Вишня», «ВИШНЯ» и «вишня» дадут точно такой же результат.

Используйте операторы сравнения со ссылками на ячейки

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

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

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

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

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

  • Звездочка (*): представляет любое количество символов
  • Знак вопроса (?): Обозначает один символ в определенной позиции.
  • Амперсанд(&): Обозначает сложение текста. В примере ниже есть описание как это работает.

Функция СУММЕСЛИ excel с примерами

Поясним примеры из скриншота выше:

  • Пример1. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение т.е в начале и конце любое количество символов.
  • Пример2. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение т.е последняя буква может быть любая. В примере я намерено допустил ошибку и написал вишна.
  • Пример3. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение. критерии включают ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) перед и после ссылки на ячейку для объединения строки.

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

Простое сложение в Excel

Складывать значения ячеек можно с помощью простой функции «СУММ», которая суммирует выбранные значения без определенного условия. Она используется, когда есть ячейки, массив или несколько массивов для сложения. В этом случае достаточно выделить нужные элементы электронной таблицы и применить функцию. Она играет роль обычного калькулятора со знаком «+».

Функция СУММ в Excel

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

Предположим у нас есть данные по продажам продуктов в магазине:


Наша задача состоит в расчете суммы продаж отдельно по овощам и по фруктам (критерий №1 в нашем примере).
В качестве диапазона выбираем ячейки A2:A13 (категория продукта), задаем условие суммирования F3 («Овощи») и выбираем в диапазон суммирования ячейки D2:D13 (объем продаж в рублях):

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

Также посчитаем объем продаж продуктов в рублях свыше определенного количества килограмм (критерий №2 в нашем примере).
В качестве диапазона выбираем ячейки C2:C13 (объем продаж в кг), задаем условие суммирования F7 (“>500”) и выбираем в диапазон суммирования ячейки D2:D13 (объем продаж в рублях):

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

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

Ссылка на основную публикацию
Adblock
detector