Суммирование ячеек в excel по условию

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

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

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

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

Что такое автосумма в таблице Excel?

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

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

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

=СУММ()

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

Кроме того, ALT + = можно просто нажать, чтобы быстро поставить формулу в ячейку Excel и не вводить ее руками. Согласитесь, этот небольшой хак в Экселе может сильно ускорить работу.

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

Под видео на всякий случай дано короткое пояснение.

Итак, выберите диапазон ячеек и дополнительно ряд пустых ячеек снизу и справа (то есть, В2:Е14 в приведенном примере).

Нажмите кнопку «Автосумма» на вкладке «Главная» ленты. Формула сложения будет сразу введена.

Суммирование по пустым и непустым ячейкам.

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

Критерии
Описание

Пустые ячейки
“=”
Суммируйте числа, соответствующие пустым, которые не содержат абсолютно ничего – ни формулы, ни строки нулевой длины.
=СУММЕСЛИМН(C2:C10;A2:A10;”=”;B2:B10,”=”) Суммируйте в C2:C10, если соответствующие ячейки в столбцах A и B абсолютно пусты.

«»
Суммируйте числа, соответствующие «визуально» пустым, включая те, которые содержат пустые строки, возвращаемые какой-либо другой функцией Excel (например, ячейки с формулой вроде = “”).
=СУММЕСЛИМН(C2:C10;A2:A10;””;B2:B10,””)   Суммируйте в C2:C10 с теми же параметрами, что и в приведенной выше формуле, но с пустыми строками.
Непустые ячейки
“”
Суммируйте числа, соответствующие непустым, включая строки нулевой длины.
=СУММЕСЛИМН(C2:C10;A2:A10;””;B2:B10,””) Суммируйте в C2: C10, если соответствующие ячейки в столбцах A и B не пусты, включая ячейки с пустыми строками.

Суммируйте числа, соответствующие непустым, не включая строки нулевой длины.
=СУММ(C2:C10) – СУММЕСЛИМН(C2:C10;A2:A10;””;B2:B10,””) или{=СУММ((C2:C10)*(ДЛСТР(A2:A10)>0)*(ДЛСТР(B2: B10)>0))} Если в столбцах A и B содержится текст ненулевой длины, тогда соответствующее число из C складывается. Внимание! Это формула массива! Фигурные скобки вводить не нужно!

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

По покупателю «Красный» рассчитаем количество товара в невыполненных заказах. Для этого в столбце B ищем соответствующее название клиента, а в F – пустую ячейку. Если оба требования совпадают, складываем количество товара из столбца D.

=СУММЕСЛИМН(D2:D21;F2:F21;””;B2:B21;”Красный”)

или

=СУММЕСЛИМН(D2:D21;F2:F21;”=”;B2:B21;”Красный”)

Каждое из этих выражений дает верный результат – 144 единицы в заказе от 4 февраля.

Сумма нескольких условий.

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

Если мы просто добавим второй критерий в I3, и вместо I2 используем область I2:I3, то расчет будет неверным, поскольку в C2:C21 будем искать товар, в названии которого есть И «черный», И «молочный» одновременно. Ведь таких просто нет.

Поэтому первый вариант расчета таков:

=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I4)+СУММЕСЛИМН(E2:E21;C2:C21;I3;F2:F21;I4)

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

Второй вариант: используем элемент массива критериев и функцию СУММ.

=СУММ(СУММЕСЛИМН(E2:E21;C2:C21;{“*молочный*”;”*черный*”};F2:F21;I4))

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

Ещё примеры расчета суммы:

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

​ Excel» тут.​ в функции СУММЕСЛИ —​ аргументом​Артишоки​ (обязательный аргумент)​припасла третий необязательный​может принимать до​ не будем рассматривать,​ например от 5​​критерию, т.е. слову​​ текстовым «Фрукты» и​​ значений? Если да-то​​ проверяемый текст (например,​ в различные страны​ качестве аргументов функции,​ и при пошаговом​ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая​

Функция СУММ – простое суммирование ячеек в Excel

​Второй пример.​​ третьим. Этот момент​​Диапазон_суммирования​Ольга​Диапазон ячеек для суммирования.​ аргумент, который отвечает​ 255 аргументов и​ как посчитать сумму​ до 20 (см.​ персики. Массив можно​ числовым «Количество на​ как это сделать?​

​ «Продавец_1», «Товар_1») или​ на протяжении трех​​ и возвращает полученное​​ вычислении на листе​ версия Excel​

​Сумма выборочных ячеек​​ часто является источником​​(A2:A9). Результат — 30.​22​Диапазон_условия1​ за диапазон, который​ суммировать сразу несколько​ в Excel при​

​ файл примера Лист​​ увидеть, выделив в​​ складе» (См. файл​ Заранее спасибо за​ числовое значение (Например,​ месяцев. Определить суммарный​ суммарное значение.​ массивы (выделены красным).​​Способ 2:​​Excel.​ проблем при использовании​Проблема​Бананы​ (обязательный аргумент)​

​ необходимо просуммировать. Т.е.​​ несмежных диапазонов или​​ помощи оператора сложения,​ 2Числа).​ Строке формул A2:A13=D2,​

​ примера).​ ответ.​ 20), определяющие ячейки​​ доход от отгрузок​​В отличие от СУММЕСЛИ,​Думаю всё понятно​Code =СУММПРОИЗВ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)​Нам нужно посчитать​ данных функций.​Описание​

​Артем​​ Диапазон, в котором проверяется​​ по первому аргументу​ ячеек:​ автосуммы и прочих​Формулы строятся аналогично задаче​ а затем нажав​

​Найдем количество ящиков товара​Юрий М​ в диапазоне условия1,​​ товара №2 в​​ в рассматриваемой функции​ :)​В английской версии:​

​ сумму всех Ашанов​При копировании и изменении​Вместо ожидаемого результата отображается​12​

​Условие1​​ функция проверяет условие,​​Если среди суммируемых значений​ инструментов. Сегодня мы​ 1: =СУММЕСЛИМН(B2:B13;B2:B13;»>=»&D2;B2:B13;»​F9​ с определенным Фруктом​: А СУММЕСЛИ() +​ для которых будут​ Казахстан за июнь​ предусмотрена возможность внесения​

​МИНУСЫ: СУММПРОИЗВ -​Code =SUMPRODUCT((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)​ с номерами, у​ этих похожих формул​

СУММЕСЛИ — условная сумма в Excel

​ 0 (нуль).​​Бананы​​.​ а третий подлежит​ содержится текст, то​ разберем всего две​Примечание​​;​​И​ СУММЕСЛИ() не подойдёт?​

​ отобраны и просуммированы​ и август.​ до 127 критериев​ «тяжёлая» формула массива.​КАК ЭТО РАБОТАЕТ:​ которых нет слов​

​ нужно следить за​Если выполняется поиск текстовых​Ольга​Диапазон_условия1​ суммированию.​

​: для удобства, строки,​Результатом вычисления B2:B13 является​, у которых Остаток​vikttur​ данные из диапазона​Вид таблицы данных:​

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

​СУММ​​ участвующие в суммировании,​​ массив , т.е.​ ящиков на складе​: =ИЛИ(СУММЕСЛИ(. ;<.>;. ))​ суммирования.​Для расчета итоговой суммы​ суммирования. СУММЕСЛИМН удобна​ больших диапазонах данных​ массивы из значений​

​В ячейке D4​Одинаковое количество строк и​ человека, убедитесь в​Морковь​Условие1​

​ просуммируем общую стоимость​​их игнорирует, т.е.​​и​

​ выделены Условным форматированием​ просто значения из​ не менее минимального.​Makes​​Последующие аргументы являются необязательными​​ используем формулу:​ для работы с​ заметно увеличивается время​ ИСТИНА и ЛОЖЬ,​

​ напишем такую формулу.​ столбцов для аргументов,​ том, что значения​Артем​составляют пару, определяющую,​​ всех проданных фруктов.​​ не включает в​​СУММЕСЛИ​​ с правилом =И($B2>=$D$2;$B2​ столбца​ Например, количество ящиков​: Слишком громоздкая формула​ для заполнения, а​Результат расчетов:​ объемными таблицами, содержащими​ пересчёта.ПРИМЕЧАНИЯ: Количество обрабатываемых​ согласно выбранным критериям,​=СУММЕСЛИ(A2:A8;D3&»*»;B2:B8)​ задающих диапазоны ячеек​ аргументов​33​ к какому диапазону​

​ Для этого воспользуемся​

СУММИРОВАТЬ все значения между двумя датами для определенного продукта

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

Например, предположим, что у вас есть тот же набор данных (как показано ниже), но на этот раз вы хотите узнать общие продажи принтеров, которые произошли между двумя указанными датами (1 января и 31 января).

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

Ниже приведена формула, которая сделает это:

= СУММЕСЛИМН (C2: C15, A2: A15, "> = 1-1-2020", $ A $ 2: $ A $ 15, "<= 31-01-2020", $ B $ 2: $ B $ 15, "Принтер" )

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

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

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

 = СУММЕСЛИМН (C2: C15, A2: A15, "> = 1-1-2020", $ A $ 2: $ A $ 15, "<= 31-01-2020", $ B $ 2: $ B $ 15, "Сканер" )

В приведенной выше формуле я использовал оператор not-equal-to-operator (), чтобы исключить получение значений для Scanner в результате.

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

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

Надеюсь, вы нашли этот урок полезным.

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

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

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

Итак, начнем с первого примера. Допустим, у нас есть таблица, в которой содержится информация о том, как справляются студенты с учебной программой по определенному предмету. Есть набор оценок, успеваемость оценивается по 10-балльной шкале. Стоит задача найти оценку за экзамен тех студентов, фамилия которых начинается с буквы А, а их минимальный балл составляет 5.

Таблица выглядит следующим образом.

1

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

2

Давайте более подробно опишем аргументы:

1. C3:C14 – это наш диапазон суммирования. В нашем случае он совпадает с диапазоном условия. Из него будут отбираться баллы, используемые для расчета суммы, но лишь те, которые подпадают под наши критерии.
2. «>5» – наше первое условие.
3. B3:B14 – второй диапазон суммирования, который обрабатывается на предмет соответствия второму критерию. Видим, что здесь нет совпадения с диапазоном суммирования. Из этого делаем вывод, что диапазон суммирования и диапазон условия может быть как идентичным, так и нет. 
4. «A*» – второй диапазон, который задает отбор оценок лишь тех студентов, чья фамилия начинается на А. Звездочка в нашем случае означает любое количество знаков. 

После расчетов мы получаем следующую таблицу.

3

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

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

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

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

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

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

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

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

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

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

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



Рекомендации

Необходимые действия	Описание
Использование подстановочных знаков	Подстановочные знаки, такие как вопросительный знак (?) или звездочка (*), в аргументах Условие1, 2 можно использовать для поиска сходных, но не совпадающих значений. Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед вопросительным знаком. Например, формула =СУММЕСЛИМН(A2:A9; B2:B9; “=Я*”; C2:C9; “Арте?”) будет суммировать все значения с именем, начинающимся на “Арте” и оканчивающимся любой буквой.
Различия между функциями СУММЕСЛИ и СУММЕСЛИМН	Порядок аргументов в функциях СУММЕСЛИ и СУММЕСЛИМН различается. Например, в функции СУММЕСЛИМН аргумент Диапазон_суммирования является первым, а в функции СУММЕСЛИ — третьим. Этот момент часто является источником проблем при использовании данных функций. При копировании и изменении этих похожих формул нужно следить за правильным порядком аргументов.
Одинаковое количество строк и столбцов для аргументов, задающих диапазоны ячеек	Аргумент Диапазон_условия должен иметь то же количество строк и столбцов, что и аргумент Диапазон_суммирования.

К началу страницы

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

В рассмотренном выше примере нужно отыскать сумму по заказам, реализованным менеджером Воробьевым И. И. заказчику ИП Васькову А. П. Таковым образом, критериев отбора два: сотрудник и заказчик. Как и в прошлом примере, ставим курсор в ячейку, где будет итог. «Формулы»-«Математические»-СУММЕСЛИмн. Возникает окно «Аргументы». Заполняется оно несколько непревзойденно от предшествующего примера.

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

Особенности функции

• Если сравнивать «суммесли» и «суммеслимн», то расположение аргументов в синтаксисе указанных операторов различается. У первой функции диапазон суммирования находится на третьей позиции, у второй – на первой.
• Если неправильно ввести данные для «суммеслимн» в Excel, примеры выявляют ошибку. Дело в том, что размерность всех строк и столбцов диапазонов одинакова для данного оператора. «Суммесли» допускает различное количество ячеек в интервале суммирования и условия.
• В случае перебора первого аргумента обнаруживаются пустые или текстовые ячейки, они игнорируются.
• При прописывании критерия используют знак «*». Он обозначает, что искать нужно содержащийся фрагмент и не стопроцентное совпадение с условием.
• Функция «суммеслимн» в Excel (примеры это подтверждают) допускают длину строки до 255 символов.
• Ячейки, указанные в диапазоне суммирования, лишь тогда складываются, когда удовлетворяют всем поставленным условиям. Иначе говоря, выполняется еще логическая функция «И».

Как в эксель подобрать слагаемые для нужной суммы

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

Взгляните на этот пример, в котором есть два условия: мы хотим получить сумму продаж «Мясо» (из столбца C) в регионе «Южный» (из столбца A).

Вот формула, с помощью которая можно сопровождать эту формулу:

=СУММЕСС(D2:D11,A2:A11,»Южный»,C2:C11,»Мясо»)

Результат — значение 14 719.

Рассмотрим каждую часть формулы более подробно.

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

=СУММЕСЛИМН(D2:D11,

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

Затем вам нужно найти данные, отвечающие двум условиям, поэтому введите первое условие, указав для функции расположение данных (A2:A11) и условие («Южный»)

Обратите внимание на запятую между аргументами:. =СУММЕСЛИМН(D2:D11;A2:A11;»Южный»;

=СУММЕСЛИМН(D2:D11;A2:A11;»Южный»;

Кавычка вокруг текста «Южный» указывает на то, что это текстовые данные.

Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово «Мясо», а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.

=СУММЕСЛИМН(D2:D11;A2:A11,»Южный»;C2:C11,»Мясо»)

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

На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится «Южный» (критерий1).

По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.

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

Попробуйте попрактиковаться

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

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

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

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

Рассмотрим простой пример, в ячейку D11 введем начало формулы «=СУММЕСЛИ(» и нажмем на кнопку Fx, вызвав тем самым мастер аргументов функции. В поле Диапазон напишем или укажем мышью ячейки «D4:D7», в поле Критерий введем условие меньше шести «<6», Диапазон_суммирования «D4:D7», нажмем ОК.

Результат 9, как и предполагалось.

Теперь проделаем ту же процедуру с ячейкой E11, введем «=СУММЕСЛИ(«, мастер Fx, Диапазон «E4:E7», Критерий больше 150 «>150»,

Диапазон_суммирования «E4:E7», нажмем ОК и получим 520.

Полностью формула выглядит так:

<code class="java">"=СУММЕСЛИ(E4:E7;">150";E4:E7)"</code>

В этом видео подробно показано применение  функции СУММЕСЛИ в excel:

Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

Новые статьи

• Исправляем ошибку VBA № 5854 слишком длинный строковый параметр в шаблоне word из таблицы excel 255 символов — 21/02/2021 08:54
• База данных из JavaScript для веб страницы из Excel на VBA модуле — 30/11/2019 09:15
• Листы в Excel из списка по шаблону — 02/06/2019 15:42
• Печать верхней строки на каждой странице в Excel — 04/06/2017 17:05
• Создание диаграммы, гистограммы в Excel — 04/06/2017 15:12
• Функция СИМВОЛ в Excel или как верстать HTML в Excel — 03/06/2017 17:32
• Функция ЕСЛИОШИБКА в excel, пример использования — 20/05/2017 11:39
• Как использовать функцию МИН в excel — 20/05/2017 11:36
• Как использовать функцию МАКС в excel — 20/05/2017 11:33
• Как использовать функцию ПРОПИСН в excel — 20/05/2017 11:31
• Как использовать функцию СТРОЧН в excel — 20/05/2017 11:29
• Как использовать функцию СЧЕТЕСЛИ в excel — 20/05/2017 11:26
• Как использовать функцию Функция СЧЁТ в excel — 20/05/2017 11:09
• Как использовать функцию ПОИСК в эксель — 10/03/2017 21:28
• Как использовать функцию СЦЕПИТЬ в эксель — 10/03/2017 20:41
• Как использовать функцию ПРАВСИМВ в excel — 10/03/2017 20:35
• Как использовать функцию ЛЕВСИМВ в excel — 06/03/2017 16:04
• Как использовать функцию ЗАМЕНИТЬ в excel — 28/02/2017 18:44
• Как использовать функцию ДЛСТР в эксель — 25/02/2017 15:07
• Как использовать функцию ЕСЛИ в эксель — 24/02/2017 19:37

Предыдущие статьи

• Как использовать функцию СУММ в эксель — 20/02/2017 19:54
• Печать документа в Excel и настройка печати — 16/02/2017 19:15
• Условное форматирование в ячейках таблицы Excel — 16/06/2016 17:38
• Объединить строку и дату в Excel в одной ячейке — 16/06/2016 17:33
• Горячие клавиши в Microsoft Office Excel — 04/06/2016 14:57
• Как использовать эксель в качестве фотошопа — 04/06/2016 09:01
• Как разделить текст по столбцам, как разделить ячейки в Excel — 14/04/2016 16:19
• Как применить функцию ВПР в Excel для поиска данных на листе — 08/01/2016 23:40
• Как создать таблицу в Excel, оформление таблицы — 06/01/2016 20:29
• Работа в эксель, как начать пользоваться Excel — 26/12/2015 15:48

СУММЕСЛИ

Аргументы

Рассмотрим аргументы для СУММЕСЛИ.

=СУММЕСЛИ(диапазон; критерий; )

Диапазон – здесь необходимо указать, тот столбец, данные в котором будут сравниваться с заданным Критерием. В примере, это D2:D26.

Критерий – здесь нужно выбрать одно значение из ранее указанного Диапазона. Сначала будем указывать мальчиков, затем девочек.

Диапазон суммирования – это столбец, значения в котором нужно просуммировать, если они относятся к выбранному Критерию. Например, если для выбранного Диапазона D2:D26 указан Критерий «мальчик», значит в столбце «Средний бал»: С2:С26, будут суммироваться все значения, которые относятся к мальчикам.

Пример

Выделяем ячейку G4 и ставим «=». Пишем в ней СУММЕСЛИ и открываем скобку «(». В качестве Диапазона, из которого будут выбираться значения, выделяем D2:D26. В качестве Критерия, нужно выбрать одно из значений в ранее указанном Диапазоне. У нас это «мальчик», поэтому выделяем ячейку D3. Диапазон суммирования – это столбец, в котором указан «Средний бал». Выделяем С2:С26. Между аргументами ставьте «;», в конце, закройте скобку «)». Нажмите «Enter».

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

Теперь давайте растянем формулу по столбцу. Конечно, можно также прописать ее и для девочек, но если у Вас много разных критериев – это займет немало времени. Закрепим диапазоны для столбцов С и D, чтобы при копировании формулы они не ползли вниз.

Для этого выделяем G4 и в «Строке формул» после D2, D26, C2, C26 нажимаем клавишу «F4». В адресах на ячейки, появятся знаки «$». Таким образом, мы делаем ссылки абсолютными. Чтобы растянуть формулу по столбцу, потяните за правый нижний угол ячейки, курсор изменит при этом вид на черный плюсик.

Если Вам интересно, как сделать ссылки в Эксель: абсолютные, относительные, на другой лист или книгу, прочтите статью по этой теме.

Дальше смотрим на «Строку формул». В качестве аргументов для Диапазона и Диапазона суммирования берутся правильные значения: D2:D26, C2:C26. В качестве Критерия укажите адрес нужной ячейки. Поскольку, нужно посчитать средний балл для всех девочек, в столбце D выбираем адрес любой ячейки, в которой указано «девочка»: D4.

Синтаксис формулы суммирования по условию в Excel

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

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

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

• сроки с текстовым значением;
• числовые значения;
• логические выражения;
• формулы с функциями;
• ссылки на ячейки.

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

Примеры использования

Рассмотрим все возможные ситуации применения одноименной функции.

Общий вид

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

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

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

• C3:C12 – тип одежды;
• «одежда» – критерий;
• F3:F12 – интервал суммирования.

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

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

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

• F3:F12 – диапазон суммирования;
• «одежда» и 50 – критерии;
• C3:C12 и E3:E12 – диапазоны типов товара и стоимости единицы соответственно.

С динамическим условием

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

Для вставки новой строки нужно нажать ПКМ на интересующей ячейке и выбрать «Вставить» – «Строку».

Далее просто введите новые данные или скопируйте их с другого места. Итоговое значение соответственно изменится. Аналогичные трансформации происходят и при редактировании или удалении строк.

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

Идея формулы

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

Мы располагаем таблицей, в которой необходимо просуммировать значения из столбца Количество, но с учётом двух критериев или фильтров, если угодно. Первый критерий – столбец Магазин должен содержать значение Центр, второй критерий – столбец Товар должен содержать значение Компьютер. Оба критерия действуют одновременно или, как говорят, по “И” (а не по “ИЛИ“). То есть нас интересует, сколько компьютеров продал центральный магазин.

Для этого в ячейку G7 мы помещаем формулу СУММЕСЛИМН, в которую передаём 5 параметров:

• D3:D15 – диапазон, содержащий числа, которые мы собираемся суммировать

• B3:B15 – диапазон, содержащий значения для сравнения с критерием 1

• G3 – ячейка, содержащая, собственно, критерий 1 – значение, которое нас интересует – “Центр“

• C3:C15 – диапазон, содержащий значения для сравнения с критерием 2

• G5 – ячейка, содержащая критерий 2 – “Компьютер“

Формула вернёт нам значение 14, так как только 2 строки таблицы удовлетворяют обоим нашим критериям, и обе они содержат число 7. А теперь давайте дадим более формальное описание данной функции.

Как суммировать весь столбец либо строку.

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

Если поставить формулу суммы в G2

то получим общую выручку по первому отделу.

Обратите внимание, что наличие текста, а не числа, в ячейке B1 никак не сказалось на подсчетах. Складываются только числовые значения, а символьные – игнорируются. Все сказанное выше в полной мере относится и к работе со строками

Все сказанное выше в полной мере относится и к работе со строками.

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

Сумма столбцов из нескольких таблиц.

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

Для получения итогов сразу по нескольким таблицам также используем функцию СУММ и структурированные ссылки. Такие ссылки появляются при создании в Excel «умной» таблицы.

При создании её Excel назначает имя самой таблице и каждому заголовку колонки в ней. Эти имена затем можно использовать в выражениях: они могут отображаться в виде подсказок в строке ввода.

В нашем случае это выглядит так:

Прямая ссылка	Структурированная ссылка (Имя таблицы и столбца)
B2:B21	Таблица2

Для создания «умной» таблицы выделим диапазон A1:B21 и на ленте «Главная» выбираем «Форматировать как таблицу».

Приятным бонусом здесь является то, что «умная» таблица сама изменяет свои размеры при добавлении в нее данных (или же их удалении), ссылки на нее корректировать не нужно.

Также в нашем случае не принципиально, где именно располагаются в вашем файле Excel эти данные

Даже не важно, что они находятся на разных листах – программа все равно найдет их по имени

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

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

Примечание: итоговая строчка в таблице должна быть включена. Если вы отключите её, то выражение вернет ошибку #ССЫЛКА.

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

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

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

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

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

Также рекомендуем:

Пример 2

Даны 4 столбца, в которых указаны:

• А – категория товара;
• В – конкретные продукты;
• С – российский город;
• D – объем продаж.

Первое условие в примере 1 — отобрать все ячейки, содержащие слово «овощи». Второй критерий — найти клетки, отвечающие за город «Москва». Во втором примере первое условие содержит поиск по фруктам. Второй критерий относится к Казани.

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

Во втором операторе цифрами помечены аргументы «суммеслимн»:

• 1 – диапазон суммирования (объем продаж);
• 2 – интервал первого условия (поиск по категории);
• 3 – критерий 1 (поиск слова «фрукты»);
• 4 – диапазон второго условия (нахождение по городу);
• 5 – критерий 2 (поиск слова «Казань»).