для чего нужна функция суммесли в excel

Функция СУММЕСЛИ

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

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

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

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

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

Синтаксис

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

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

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

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

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

Источник

Excel-plus

Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel

Функция СУММЕСЛИ в MS Excel. Варианты данной функции с описанием и примерами.

Функция СУММЕСЛИ в MS Excel. Варианты данной функции с описанием и примерами.

Функция СУММЕСЛИ Excel.

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

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

Возможные варианты данной функции:

Отдельная статья описывает функцию СУММЕСЛИ Excel. Точное совпадение. Частичное совпадение. С текстовыми критерий. Содержит описание, примеры и иллюстрации. Статья находиться по этой ссылке: Функция СУММЕСЛИ в MS Excel. Точное совпадение. Частичное совпадение. Инструкция и примеры.

Самый простой и понятный способ вызвать данную функцию, это нажать в правом верхнем углу, рядом с кнопкой ∑, стрелку вниз (Рис. 1).

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

Появляется диалоговое окно – Вставка функции. Самое верхнее, это поле для поиска нужной функции. Вводим название функции, выбираем ее в результатах поиска и нажимаем ОК (Рис. 3).

Появляется диалоговое окно — Аргументы функции (Рис. 4).

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

Что бы выбрать нужный диапазон нажимаете на стрелочку в конце строки диапазона и выбираете нужные Вам ячейки с помощью мыши. Можно сразу выбрать нужный диапазон, не нажимая на стрелочку, но сначала нужно поставив курсор на нужную строку в диалоговом окне. Можно прописать диапазон вручную. Результат от этого не зависит. После того как все готово, нажимает ОК (Рис. 5).

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

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

Сумма, если больше, Сумма, если меньше. СУММЕСЛИ.

Знаки, которые используются: >, 500»)

Напротив каждого примера стоит число от 100 до 1300. Нам нужно просуммировать, только значения, которые больше числа 500.

Вызываем функцию, как описано выше и выбираем нужный нам диапазон в строке – Диапазон (Е3;Е27). Это столбец со значениями чисел от 100 до 1300.

Вводим критерий. В строке Критерий, пишем: >500, вручную. Можно скопировать значение критерия с самой таблицы и добавить в ручную только знак >. Кавычки появятся автоматически (Рис. 7).

Нажимаем ОК. Напротив ячейки с словом ИТОГО, появилась сумма чисел больше 500 (Рис. 8)

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

Формула в ячейке, как пример, выглядит вот так:

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

Формулы в ячейках, как пример, выглядят вот так:

В столбце D, есть четыре ячейки с Пример 1 (выделенные красным шрифтом). Наша задача суммировать значения, которые стоят только напротив Пример 1. Вызываем функцию СУММЕСЛИ. В Диапазоне выбираем нужный диапазон (столбец D) (D3:D27).

В Критерий, вручную пишем Пример 1 или копируем с таблицы. По этому критерию будет осуществляться поиск нужных ячеек в диапазоне (D3:D27).

В Диапазон_суммирования выбираем диапазон ячеек с значениями (столбец Е), которые будут суммироваться (E3:E27). Суммироваться будут те ячейки, которые будут отвечать нашему критерию и находиться напротив ячеек с значением Пример 1. Нажимаем ОК (Рис. 9).

Так же можно использовать данный вариант функции, когда из списка значений (цифр), нужно суммировать только цифры равные определенному значению. Например, в диапазоне значений (E3:E27), нам нужно суммировать только значения равные числу 100 (выделенные красным шрифтом). В строке Критерий указываем необходимое значений, после знака =. В нашем случае это цифра 100. Диапазон_суммирования не заполняем. (Рис. 10).

Получаем искомую сумму, в нашем случае это число 400.

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

Формулы в ячейках, как пример, выглядят вот так:

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

Выбираем в Диапазон ячейки, содержащие наш критерий (D3:D27) – Пример 1

В Критерий прописываем: <> Пример 1. Получаем сумму всех значений (диапазон E3:E27), кроме тех, которые стоят напротив Пример 1 (диапазон (D3:D27)).

В Диапазон_суммирования выбираем ячейки, значения которых нужно суммировать (E3:E27). Нажимаем ОК. Получаем нужную нам сумму. (Рис. 11).

Или нам нужно суммировать только значение не равные числу 100.

Тогда в строке Диапазон указываем нужный нам диапазон для суммирования (E3:E27).

В Критерий пишем <>100.

Диапазон_суммирования не заполняем.

Нажимаем ОК. Получаем сумму всех ячеек, кроме тех, где значение равно числу 100. (Рис. 12).

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

Знаки, которые используются: >=, =500″).

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

В строке Диапазон выбираем диапазон ячеек, которые нужно просуммировать (E3:E27).

В Критерий пишем:>=500.

Диапазон_суммирования не заполняем.

Нажимаем ОК. Получаем сумму значений равною числу 500, плюс все, что больше этой цифры в заданном диапазоне (E3:E27) (Рис. 13).

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

Формула в ячейке, как пример, выглядит вот так:

Источник

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

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

Описание функции СУММЕСЛИ

СУММЕСЛИ(диапазон; условие; диапазон_суммирования)
Суммирует ячейки, заданные указанным условием.

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

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

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

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

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

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

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

Источник

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

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

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

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

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

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

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

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

Вот так просто оно работает на практике. Общая сумма денег, которая была получена составила 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, то в качестве критерия будет использоваться «вчера».

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

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

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

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

10

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

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

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

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

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

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

11

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

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

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

Источник

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

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

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

Синтаксис

Как видите, функция СУММЕСЛИ имеет 3 аргумента: первые 2 обязательны, а третий — необязательный.

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

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

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

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

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

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

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

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

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

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

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

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

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

Формулы СУММЕСЛИ с условием даты

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

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

Источник