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

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

Синтаксис и создание функции

Функция СУММЕСЛИ популярна, поскольку почти в каждой таблице нужно посчитать сумму чисел в ячейках, игнорируя значения, не попадающие под основное условие. Благодаря этой формуле подсчет не становится чем-то сложным и длительным. Стандартное оформление функции выглядит как =СУММЕСЛИ(Диапазон;Критерий;Диапазон_суммирования), а «Диапазон суммирования» указывается только при условии, когда есть фактические ячейки, сложение которых выполняется при любых обстоятельствах. Если данные в «Диапазоне суммирования» отсутствуют, проверяться будут все ячейки, входящие в «Диапазон».

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

Функция СУММЕСЛИ при условии неравенства

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

    Определитесь с диапазоном ячеек, попадающих под рассмотрение формулой, у нас это будет прибыль за месяц.

Создайте открывающую и закрывающую скобку, где введите диапазон выбранных ячеек, например C2:C25. После этого обязательно поставьте знак ;, который означает конец аргумента.

Откройте кавычки и в них укажите условие, что в нашем случае будет >300000.

Как только произойдет нажатие по клавише Enter, функция активируется. На скриншоте ниже видно, что условию >300000 соответствуют лишь две ячейки, следовательно, формула суммирует их числа и отображает в отдельном блоке.

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

Функция СУММЕСЛИ при условии соответствия тексту

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

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

Начните запись функции с ее обозначения точно так же, как это уже было показано выше.

В первую очередь введите диапазон надписей, поставьте ; и задайте условие. Тогда это выражение в синтаксическом формате обретет примерно такой вид: A2:A25;«Сентябрь»;.

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

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

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

Функция СУММЕСЛИМН с несколькими условиями

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

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

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

Следом идет второй аргумент — совпадение по названию в ячейке. Запись этого параметра уже рассматривалась выше и еще раз ее можно увидеть на скриншоте.

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

  • Полностью строка этой функции имеет вид =СУММЕСЛИМН(C2:C25;A2:A25;«Сентябрь»;B2:B25;«>29»), а вам остается лишь подстроить ее под себя и получить правильный результат. Может показаться, что объявлять каждый аргумент и не запутаться в символах сложно, но при раздельном подходе никаких проблем возникнуть не должно.
  • СУММЕСЛИ и СУММЕСЛИМН относятся к математическим функциям, которые имеют схожее синтаксическое представление, но используются в разных ситуациях. Для чтения информации по всем популярным формулам в Excel обратитесь к материалу по ссылке ниже.

    Источник:
    http://lumpics.ru/examples-of-the-function-sumif-in-excel/

    СУММЕСЛИ (функция СУММЕСЛИ)

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

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

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

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

    Важно: Функция СУММЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 знаков или строки #VALUE!.

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

    Аргументы функции СУММЕСЛИ описаны ниже.

    Диапазон Обязательный аргумент. Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel (см. примеры ниже).

    Условие .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Подстановочные знаки можно включать в вопросительный знак (?), чтобы они соответствовали любому символу, звездочку (*) в соответствии с любой последовательностью знаков. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак «тильда» (

    Например, условие может быть выражено в виде 32, «>32», B5, «3?», «Apple *», «*

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

    Диапазон_суммирования .Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие).

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

    Источник:
    http://support.microsoft.com/ru-ru/office/%D1%81%D1%83%D0%BC%D0%BC%D0%B5%D1%81%D0%BB%D0%B8-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D1%81%D1%83%D0%BC%D0%BC%D0%B5%D1%81%D0%BB%D0%B8-169b8c99-c05c-4483-a712-1697a653039b

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

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

    Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel, с 2016 по 2003 год. Еще одна приятная новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие «ЕСЛИ»-функции, такие как СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН и т.д.

    Как пользоваться СУММЕСЛИ в Excel – синтаксис

    Её назначение – найти итог значений, которые удовлетворяют определённым требованиям.

    Синтаксис функции выглядит следующим образом:

    =СУММЕСЛИ(диапазон, критерий, [диапазон_суммирования])

    Диапазон – это область, которую мы исследуем на соответствие определённому значению.

    Критерий – это значение или шаблон, по которому мы производим отбор чисел для суммирования.

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

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

    Диапазон_суммирования — третий параметр, который является необязательным, однако он весьма полезен. Благодаря ему мы можем производить поиск в одной области, а суммировать значения из другой в соответствующих строках.

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

    Чтобы лучше понять это описание, рассмотрим несколько простых задач. Надеюсь, что они будут понятны не только «продвинутым» пользователям, но и подойдут для «чайников».

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

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

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

    D2:D21 – это координаты, в которых мы ищем значение.

    I2 – ссылка на критерий отбора. Иначе говоря, мы ищем ячейки со значением 144 и складываем их.

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

    Кроме того, в качестве задания для отбора нужных значений можно указать текстовое выражение, состоящее из знаков >, , = и числа.

    Можно указать его прямо в формуле, как это сделано в I13

    =СУММЕСЛИ(D2:D21;»

    В I3 запишем выражение:

    F2:F21 – это область, в которой мы отбираем подходящие значения.

    I2 – здесь записано, что именно отбираем.

    E2:E21 – складываем числа, соответствующие найденным совпадениям.

    Конечно, можно указать параметр отбора прямо в выражении:

    Но мы уже договорились, что так делать не совсем рационально.

    Важное замечание. Не забываем, что все текстовые значения необходимо заключать в кавычки.

    Подстановочные знаки для частичного совпадения.

    При работе с текстовыми данными часто приходится производить поиск по какой-то части слова или фразы.

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

    Мы можем производить поиск и подсчет значений, указывая не всё содержимое ячейки, а только её часть. Таким образом мы можем расширить границы поиска, применив знаки подстановки “?”, “*”.

    Символ “?” позволяет заменить собой один любой символ.

    Символ ”*” позволяет заменить собой не один, а любое количество символов (в том числе ноль).

    Эти знаки можно применить в нашем случае двумя способами. Либо прямо вписать их в таблицу –

    =СУММЕСЛИ(C2:C21;I2;E2:Е21) , где в E2 записано *[слово]*

    =СУММЕСЛИ(C2:C21;»*»&I2&»*»;E2:E21)

    где * вставлены прямо в выражение и «склеены» с нужным текстом.

    • “*черный*” — мы ищем фразу, в которой встречается это выражение, а до него и после него – любые буквы, знаки и числа. В нашем случае этому соответствуют “Черный шоколад” и “Супер Черный шоколад”.
    • “Д?” — необходимо слово из 2 букв, первая из которых “Д”, а вторая – любая. В нашем случае подойдет “Да”.
    • “. ” — найдем слово из любых 3 букв
    Читайте также  Как вставить название над таблицей в формате Excel?

    Этому требованию соответствует “Нет”.

    • “. *” — текст из любых 7 и более букв.

    Подойдет “Зеленый”, “Оранжевый”, “Серебряный”, “Голубой”, “Коричневый”, “Золотой”, “Розовый”.

    • “З*” — мы выбираем фразу, первая буква которой “З”, а далее – любые буквы, знаки и числа. Это “Золотой” и “Зеленый”.
    • “Черный*” — подходит фраза, которая начинаются именно с этого слова, а далее – любые буквы, знаки и числа. Подходит “Черный шоколад”.

    Примечание. Если вам необходимо в качестве задания для поиска применять текст, который содержит в себе * и ?, то используйте знак тильда (

    ), поставив его перед этими символами. Тогда * и ? будут считаться обычными символами, а не шаблоном:

    Важное замечание. Если в вашем тексте для поиска встречается несколько знаков * и ?, то тильду (

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

    А если текст просто содержит в себе 3 звездочки, то можно наше выражение переписать так:

    Точная дата либо диапазон дат.

    Если нам нужно найти сумму чисел, соответствующих определённой дате, то проще всего в качестве критерия указать саму эту дату.

    Примечание. При этом не забывайте, что формат указанной вами даты должен соответствовать региональным настройкам вашей таблицы!

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

    Рассчитываем итог продаж за сегодняшний день – 04.02.2020г.

    Рассчитаем за вчерашний день.

    =СУММЕСЛИ(A2:A21;СЕГОДНЯ()-1;E2:E21)

    СЕГОДНЯ()-1 как раз и будет «вчера».

    Складываем за даты, которые предшествовали 1 февраля.

    А если нас интересует временной интервал «от-до»?

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

    =СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21) — СУММЕСЛИ(A2:A21;»

    Если критерий указать просто “*”, то мы учитываем для подсчета непустые ячейки, в которых имеется хотя бы одна буква или символ (кроме пустых).

    Точно такой же результат даёт использование вместо звездочки пары знаков «больше» и «меньше» — <>.

    Теперь рассмотрим, как можно находить сумму, соответствующую пустым ячейкам.

    Для того, чтобы найти пустые, в которых нет ни букв, ни цифр, в качестве критерия поставьте парные одинарные кавычки ‘’, если значение критерия указано в ячейке, а формула ссылается на неё.

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

    =СУММЕСЛИ(F2:F21;«»;E2:E21)

    Сумма по нескольким условиям.

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

    Вновь вернемся к нашему случаю с заказами. Рассмотрим два условия и найдем, сколько всего сделано заказов черного и молочного шоколада.

    1. СУММЕСЛИ + СУММЕСЛИ

    Находим сумму заказов по каждому виду товара, а затем просто их складываем. Думаю, с этим вы уже научились работать :).

    Это самое простое решение, но не самое универсальное и далеко не единственное.

    2. СУММ и СУММЕСЛИ с аргументами массива.

    Вышеупомянутое решение очень простое и может выполнить работу быстро, когда критериев немного. Но если вы захотите работать с несколькими, то она станет просто огромной. В этом случае лучшим подходом является использование в качестве аргумента массива критериев. Давайте рассмотрим этот подход.

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

    Если вы хотите найти покупки этих двух товаров, то ваши критерии в виде массива будут выглядеть так:

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

    А теперь воспользуемся функцией СУММ, которая умеет работать с массивами данных, складывая их содержимое.

    =СУММ(СУММЕСЛИ($C$2:$C$21; ;$E$2:$E$21))

    Важно, что результаты вычислений в первом и втором случае совпадают.

    3. СУММПРОИЗВ и СУММЕСЛИ.

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

    Вот как это будет выглядеть:

    в H3 и H4 мы запишем критерии отбора.

    Но, конечно, ничто не мешает вам перечислить значения в виде массива критериев:

    =СУММПРОИЗВ(СУММЕСЛИ(C2:C21; ;E2:E21))

    Результат, возвращаемый в обоих случаях, будет идентичен тому, что вы наблюдаете на скриншоте.

    Важное замечание! Обратите внимание, что все перечисленные выше три способа производят расчет по логическому ИЛИ. То есть, нам нужны продажи шоколада, который будет или черным, или молочным.

    Почему СУММЕСЛИ у меня не работает?

    Этому может быть несколько причин. Иногда ваше выражение не возвращает того, что вы ожидаете, только потому, что тип данных в ячейке или в каком-либо аргументе не подходит для нее. Итак, вот что нужно проверить.

    1. «Диапазон данных» и «диапазон суммирования» должны быть указаны ссылками, а не в виде массива.

    Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-нибудь еще, например, массив <1,2,3>, Excel выдаст сообщение об ошибке.

    Правильно: =СУММЕСЛИ(A1:A3, «цвет», C1:C3)

    Неверно : =СУММЕСЛИ(<1,2,3>, «цвет», C1:C3)

    2. Ошибка при суммировании значений из других листов или рабочих книг

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

    Найдем сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A, и если среди них содержатся «яблоки»:

    Однако это перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся в таком виде в текущей книге. А поскольку в аргументах 1 и 3 массивы не допускаются, то формула выдает ошибку #ЗНАЧ!.

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

    Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel они не обязательно должны иметь одинаковый размер. Но вот в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако, даже в самых последних версиях Excel 2010 и Excel 2016 сложные выражения, в которых диапазон сложения имеет меньше строк и/или столбцов, чем диапазон поиска, являются капризными. Вот почему рекомендуется всегда иметь их одинакового размера и формы.

    Источник:
    http://mister-office.ru/funktsii-excel/sumif-function.html

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Суммирует значения в ячейках 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. Суммирование значений, основанные на частичном совпадении

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

    =СУММЕСЛИ(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) – учитывает пустые ячейки, содержащие строки нулевой длины, возвращаемые некоторыми другими формулами, например =»».

    Читайте также  Где в excel 2018 можно установить режим отображения формул в ячейках

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

    Пример 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 вы можете использовать функцию СУММЕСЛИМН, которая позволяет использовать несколько условий. Эту функцию мы рассмотрим в следующей статье. А так как данная статья посвящена функции СУММЕСЛИ, то приведем пример использования СУММЕСЛИ в диапазоне дат:

    =СУММЕСЛИ(B2:B9; «>=01.11.2017»; C2:C9) — СУММЕСЛИ(B2:B9; «>=01.12.2017»; C2:C9)

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

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

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

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

    Источник:
    http://naprimerax.org/posts/68/funktciia-summesli-v-excel

    Для чего нужна функция СУММЕСЛИ в Excel (примеры применения)

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

    Общие сведения о функции СУММЕСЛИ

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

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

    Эта функция имеет следующие аргументы:

    1. Диапазон поиска. Это набор ячеек, которые будут проверяться на предмет соответствия условиям.
    2. Условие. Этот аргумент указывается в кавычках. Это непосредственно тот критерий, при котором ячейки будут суммироваться.
    3. Диапазон суммирования. Если нужно проверить определённые ячейки на предмет соответствия указанному критерию, используется этот аргумент. Он необязательный. Если его не указывать, суммирование будет осуществляться в диапазоне поиска.

    Как работает функция СУММЕСЛИ в Эксель

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

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

    Давайте опишем аргументы.

    1. В качестве диапазона поиска используется колонка с должностями. Конечно, заголовок колонки не входит в него.
    2. Используемое условие – продавец. Не стоит забывать заключать этот аргумент в кавычки.
    3. В нашем случае в качестве диапазона суммирования используется заработная плата. Соответственно, значение этого аргумента – F2:F14.

    1

    Получившийся результат – 92900. Согласитесь, что это значительно удобнее, чем вручную анализировать список профессий, проверять их на соответствие определенному условию, а потом ещё и суммировать эти значения. Особенно, если таблица большая.

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

    Функция СУММЕСЛИ в Excel с несколькими условиями

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

    Возможно использование неограниченного количества аргументов, но минимально необходимо указать 5 штук.

    1. Диапазон суммирования. Здесь это главный аргумент, который нужно обязательно указывать. Значение то же самое – указание конкретных ячеек, которые нужно просуммировать.
    2. Диапазон первого условия. Аргумент, эквивалентный диапазону поиска в функции СУММЕСЛИ. Только в этом случае указывает диапазон первого критерия.
    3. Первое условие.
    4. Диапазон второго условия. Аргументы, аналогичный диапазону первого условия.
    5. Второе условие.

    Дальше логика такая же. Указывается диапазон поиска, а потом непосредственно критерий. Таким образом, по мере увеличения количества критерии, количество аргументов увеличивается в арифметической прогрессии, где шаг равен двум: 5,7,9,11 и так далее.

    Пример применения

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

    1. Они являются женщинами.
    2. Они являются продавцами.

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

    В нашем случае аргументы будут следующими:

    1. В качестве диапазона суммирования оставляем тот же диапазон, что и в прошлом примере (поскольку там содержатся зарплаты).
    2. Диапазон условия 1 – профессия работника.
    3. Условие 1 – продавец.
    4. Диапазон условия 2 – пол работника.
    5. Условие 2 – женский

    2

    Вот так просто оно работает на практике. Общая сумма денег, которая была получена составила 51100 рублей.

    Функция СУММЕСЛИ с динамическим условием

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

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

    3

    После того, как в общий список была добавлена информация о сотруднице, формула автоматически пересчитала значения, исходя из новых данных. Функции отреагировали на добавление в список еще одной продавщицы.

    4

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

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

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

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

    Сумма по нескольким условиям

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

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

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

    5

    Нами была использована формула =СУММЕСЛИ($C$2:$C$21;”*”&H3&”*”;$E$2:$E$21)+СУММЕСЛИ($C$2:$C$21;”*”&H4&”*”;$E$2:$E$21)

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

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

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

    6

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

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

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

    Как видим, результаты вычисления в обоих случаях одинаковые.

    Еще один вариант использования нескольких критериев – сочетание функций СУММЕСЛИ и СУММПРОИЗВ . Это дает возможность перечислять условия в отдельной части диапазона.

    Формула будет такой.

    Как видим из этой формулы, критерии записываются в ячейки с адресами H3 и H4. Но также возможно использование массива критериев, как в предыдущем примере.

    7

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

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

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

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

    8

    Чтобы учесть непустые ячейки, в качестве критерия используется символ звездочка (*). В нашем случае формула будет следующей.

    Аналогичный итог можно получить, если использовать такой набор символов – <>.

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

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

    Точная дата, диапазон дат

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

    Важно учитывать, что формат должен соответствовать тому, который указан в таблице.

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

    9

    Теперь остается рассчитать результаты продаж за сегодня. Это можно сделать как через указание даты во втором аргументе (или ячейки), так и записав функцию СЕГОДНЯ() .

    Если использовать в аргументе СЕГОДНЯ()-1, то в качестве критерия будет использоваться «вчера».

    Подстановочные знаки для частичного совпадения

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

    1. ? – замена любого символа.
    2. * – замена какого-угодно количества символов.

    Например, три знака вопроса соответствует любому слову, которое состоит из трех букв.

    Чтобы в качестве аргумента использовать значение, содержащее сами символы ? и *, то нужно перед ними написать знак

    . Тогда они будут считаться обычным текстом, а не шаблоном.

    10

    Критерии для текста

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

    Не стоит забывать все текстовые значения заключать в кавычки.

    Сумма если «больше чем», «меньше», «равно»

    В таком случае перед аргументом, заключенным в кавычки, нужно написать соответствующий логический оператор. Например, так.

    =СУММЕСЛИ(D2:D21;” 11

    Почему функция СУММЕСЛИ может не работать?

    Существует несколько распространенных ошибок, по которым функция СУММЕСЛИ перестает работать.

    1. Диапазоны должны указываться в виде ссылок на диапазон, а не массива. Эта ошибка бывает довольно редко, но это возможно, поскольку многие новички не полностью понимают разницу между диапазоном и массивом.
    2. Если суммируются значения из других листов или книг, которые закрыты на момент использования формулы.
    3. Диапазон данных и поиска отличаются по размеру.

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

    Источник:
    http://office-guru.ru/excel/functions-matematicheskie/dlya-chego-nuzhna-funkciya-summesli-v-excel-primery-primeneniya.html

    Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям

    Представим себе таблицу, в которой в строках вперемешку указаны названия отделов(или счетов, или еще чего-то).

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

    Всего для СУММЕСЛИ предусмотрено три аргумента: Диапазон, Критерий, Диапазон_Суммирования.
    =СУММЕСЛИ( A1:A20000 ; A1 ; B1:B20000 )
    =SUMIF(A1:A20000,A1,B1:B20000)

    • Диапазон ( A1:A20000 ) — указывается диапазон с критериями. Т.е. столбец, в котором искать значение, указанное аргументом Критерий.
    • Критерий ( A1 )- значение(текстовое или числовое, а так же дата), которое необходимо найти в Диапазоне. Может содержать символы подстановки «*» и «?». Т.е. указав в качестве Критерия «*масса*» будут просуммированы значения, в которых встречается слово «масса». При этом слово » масса » может либо встречаться в любом месте текста, либо в ячейке может быть только одно это слово. А указав » масса* «, будут просуммированы все значения, начинающиеся на «масса». «?» — заменяет лишь один символ, т.е. указав » мас?а » вы сможете просуммировать строки и со значением «масса» и со значением «маска» и т.д.
      Если критерий записан в ячейке и надо все же использовать подстановочные символы, то можно сделать ссылку на эту ячейку добавив нужное. Допустим, надо просуммировать значения, содержащие слово » итог «. Слово » итог » записано в ячейке A1 , в столбце A при этом могут встречаться различные по написанию значения, содержащие слово «итог» : «итоги за июнь» , «итоги за июль» , «итоги за март» . Формула тогда должна выглядеть так:
      =СУММЕСЛИ( A1:A20000 ;»*»& A1 &»*»; B1:B20000 )
      «*»& A1 &»*» — знак &(амперсанд) объединяет несколько значений в одно. Т.е. в результате получится «*итог*».
      Чтобы лучше понять принцип работы формул лучше использовать инструмент Вычислить формулу: Как просмотреть этапы вычисления формул
      Все текстовые критерии и критерии с логическими и математическими знаками необходимо заключать в двойные кавычки ( =СУММЕСЛИ( A1:A20000 ;»итог»; B1:B20000 ) ). Если критерием является число, использовать кавычки не требуется. Если требуется найти непосредственно вопросительный знак или звездочку, необходимо поставить перед ним знак «тильды» (

    ).
    Про тильду и её особенности можно узнать в этой статье: Как заменить/удалить/найти звездочку?

  • Диапазон_Суммирования ( B1:B20000 )(необязательный аргумент) — указывается диапазон сумм или числовых значений, которые необходимо просуммировать.
  • Как это работает: функция ищет в Диапазоне значение, указанное аргументом Критерий, и при нахождении совпадения суммирует данные, указанные аргументом Диапазон_Суммирования. Т.е. если у нас в столбце А название отдела, а в столбце В суммы, то указав в качестве критерия «Отдел развития» результатом функции будет сумма всех значений столбца В, напротив которых в столбце А встречается «Отдел развития». Фактически Диапазон_Суммирования может не совпадать по размеру с аргументом Диапазон и ошибки самой функции это не вызовет. Однако при определении ячеек для суммирования, в качестве начальной ячейки для суммирования будет использована верхняя левая ячейка аргумента Диапазон_Суммирования , а затем суммируются ячейки, соответствующие по размеру и форме аргументу Диапазон .

    Некоторые особенности
    Последний аргумент функции(Диапазон_Суммирования — B1:B20000 ) является необязательным. А это значит, что его можно не указывать. Если его не указать, то функция просуммирует значения, указанные аргументом Диапазон. Для чего это нужно. Например, Вам необходимо получить сумму только тех чисел, которые больше нуля. В столбце А суммы. Тогда функция будет иметь такой вид:
    =СУММЕСЛИ( A1:A20000 ;»>0″)

    Что следует стоит учитывать: диапазон_суммирования и диапазон должны быть равны по количеству строк. Иначе можно получить неверный результат. Оптимально, если это будет выглядеть как в приведенных мной формулах: диапазон и диапазон_суммирования начинаются с одной строки и имеют одинаковое количество строк: A1:A20000 ; B1:B20000

    Суммирование по двум и более критериям
    Но что делать, когда критериев для суммирования 2 и больше? Допустим, Вам надо просуммировать только те суммы, которые относятся к одному отделу и только за определенную дату. Счастливые обладатели версий офиса 2007 и выше могут воспользоваться функцией СУММЕСЛИМН:
    =СУММЕСЛИМН( $C$2:$C$50 ; $A$2:$A$50 ; $I$3 ; $B$2:$B$50 ; $H8 )
    $C$2:$C$50 — диапазон_суммирования. Первым аргументов указывается диапазон ячеек, содержащих суммы, которые и будут собираться в одну.
    $A$2:$A$50 , $B$2:$B$50 — Диапазон_критерия. Указывается диапазон ячеек, в которых необходимо искать совпадение по критерию.
    $I$3 , $H8 — критерий. Здесь, как и в СУММЕСЛИ, допускается указание символов подстановки * и ? и работают они так же.

    Особенность указания аргументов: сначала указывается диапазон критерия(они пронумерованы) затем через точку-с-запятой указывается непосредственно значение(критерий), которое в этом диапазоне необходимо найти — $A$2:$A$50 ; $I$3 . И никак иначе. Не стоит пытаться сначала указать все диапазоны, а потом критерии к ним — функция выдаст либо ошибку, либо просуммирует не то, что надо.

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

    Т.к. СУММЕСЛИМН появилась только в версиях Excel, начиная с 2007, то как же быть в таких случаях несчастным пользователям более ранних версий? Очень просто: использовать другую функцию — СУММПРОИЗВ. Не буду расписывать аргументы, т.к. их много и все они являются массивами значений. Данная функция перемножает массивы, указанные аргументами. Я постараюсь описать общий принцип использования этой функции для суммирования данных по нескольким условиям.
    Для решения задачи суммирования по нескольким критериям функция будет выглядеть так:
    =СУММПРОИЗВ(( $A$2:$A$50 = $I$3 )*( $B$2:$B$50 = H5 ); $C$2:$C$50 )
    $A$2:$A$50 — диапазон дат. $I$3 — дата критерия, за которую необходимо просуммировать данные.
    $B$2:$B$50 — наименования отделов. H5 — наименование отдела, данные по которому необходимо просуммировать.
    $C$2:$C$50 — диапазон с суммами.

    Разберем логику, т.к. многим она будет совершенно не ясна просто при взгляде на данную функцию. Хотя бы потому, что в справке подобное её применение не описывается. Для большей читабельности уменьшим размеры диапазонов:
    =СУММПРОИЗВ(( $A$2:$A$5 = $I$3 )*( $B$2:$B$5 = H5 ); $C$2:$C$5 )
    Итак, выражение ( $A$2:$A$5 = $I$3 ) и ( $B$2:$B$5 = H5 ) являются логическими и возвращают массивы логических ЛОЖЬ и ИСТИНА. ИСТИНА, если ячейка диапазона $A$2:$A$5 равна значению ячейки $I$3 и ячейка диапазона $B$2:$B$5 равна значению ячейки H5 . Т.е. получается у нас следующее:
    =СУММПРОИЗВ(<ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ>*<ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ>; $C$2:$C$50 )
    Как видно, в первом массиве два совпадения условию, а во втором одно. Далее эти два массива перемножаются(за это отвечает знак умножения(*)). При перемножения происходит неявное преобразование массивов ЛОЖЬ и ИСТИНА в числовые константы 0 и 1 соответственно(<0;1;1;0>*<0;0;1;0>). Как известно, при умножении на нуль получаем нуль. И в результате получается один массив:
    =СУММПРОИЗВ(<0;0;1;0>; $C$2:$C$50 )
    Далее происходит уже перемножение массива <0;0;1;0>на массив чисел в диапазоне $C$2:$C$50 :
    =СУММПРОИЗВ(<0;0;1;0>;<10;20;30;40>)
    И как результат получаем 30. Что нам и требовалось — мы получаем лишь ту сумму, которая соответствует критерию. Если сумм, удовлетворяющих критерию будет больше одной, то они будут просуммированы.

    Преимущество СУММИРОИЗВ
    Если у аргументов вместо знака умножения указать знак плюс:
    ( $A$2:$A$5 = $I$3 )+( $B$2:$B$5 = H5 )
    то условия будут сравниваться по принципу ИЛИ: т.е. суммироваться итоговые суммы будут в случае, если хотя бы одно условие выполняется: или $A$2:$A$5 равна значению ячейки $I$3 или ячейка диапазона $B$2:$B$5 равна значению ячейки H5 .
    В этом преимущество СУММПРОИЗВ перед СУММЕСЛИМН. СУММЕСЛИМН не может суммировать значения по принципу ИЛИ, только по принципу И(все условия должны выполняться).

    Недостатки
    В СУММПРОИЗВ невозможно использовать символы подстановки * и ?. Точнее использовать можно, но они будут восприняты не как спец.символы, а как непосредственно звездочка и вопр.знак. Я считаю это существенным минусом. И хотя это можно обойти, использую внутри СУММПРОИЗВ иные функции — все же было бы замечательно, если бы функция каким-то образом могла использовать символы подстановки.

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

    Сумма по нескольким критериям (41,5 KiB, 11 787 скачиваний)

    Статья помогла? Поделись ссылкой с друзьями!

    Поиск по меткам

    почему в некоторых строках при копировании или протягивании формул СУУММЕСЛИ не находит соответствие значению ячейки в критерии при использовании синтаксиса типа H96&»*» а при отсутствии &»*» формула находит соответствие? При этом у остальных значений работают оба варианта, есть странная зависимость, исключаются все значения порядка 8хххх при использовании в сочетании с *. Что посоветуете с чего начать поиск?

    Сложно сказать. Возможно, там числа, а звездочка работает в паре с текстом. Если речь именно про цифры, то лучше использовать конструкции типа «>»&H96&»0» .

    Огромное спасибо. Профессионально и по делу.

    Источник:
    http://www.excel-vba.ru/chto-umeet-excel/funkciya-summesli-a-tak-zhe-summesli-po-dvum-kriteriyam/