Функция суммеслимн в excel и суммирование по нескольким условиям

Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования

Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.

Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) . На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.

Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20

Скачать заметку в формате Word, примеры в формате Excel

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

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

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

Рис. 2. Суммирование ячеек, отвечающих одному условию

Если у вас несколько условий, можно использовать функцию СУММЕСЛИМН (рис. 3).

Рис. 3. Суммирование ячеек, отвечающих нескольким условиям

Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.

В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).

Рис. 4. Использование функций баз данных

Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите Макросы -> Макросы; в окне Макрос, выделите строку СумЦветУсл, и нажмите Выполнить. Запуститься макрос, выберите диапазон суммирования и критерий. Ответ появится в окне.

Функции СЧЁТ и СУММ в Excel

• ​ комп стал сильно​
• ​ для заполнения, а​
• ​Результат расчетов:​
• ​ большое число полей​
• ​ формулу напишем так.​
• ​ в разделе «Библиотека​

​ аргумента, последний –​ и включить строку​ количество ящиков попадает​А​ текстовым «Фрукты» и​ на одном дата​ с одномерными массивами.​Заранее благодарен!​

​ выборку но тоже​ нужна..Как Вы думаете​ сделал таблицу с​​ виснуть, сначала стал​​ их смысл соответствует​

СЧЕТЕСЛИ

​=СУММЕСЛИМН(D2:D8;B2:B8;»Васечкин»;A2:A8;»Иванов»;C2:C8;»яблоки»)+​ функций» выбираем «Математические».​ это диапазон для​ Итогов.​​ в определенный интервал,​​критерию, т.е. слову​

​Если продублировать Прибытие​sboy​ — не получается​ почему может зависать​ДАТАМИ​​ думать что проблема​​ аргументам диапазон_условия1; условие1.​

СУММ

​СУММЕСЛИМН(D2:D8;B2:B8;»Сергеева»;A2:A8;»Иванов»;C2:C8;»яблоки»)​ В появившемся списке​​ суммирования.​​СЧЁТ​

СУММЕСЛИ

​ складе» (См. файл​ на другом столбец​ то формула будет:​: Добрый день.​​zzbear​​ комп при открытии​, куда собственно и​ сама в компе,​ Всего может быть​ что для столбца​ сумму средств, полученных​Пишем эту формулу​

​СЧЁТЕСЛИ​ до 20 (см.​ увидеть, выделив в​ примера).​​ с датами и​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН(J2:K51;C2:D51;»Орск»;H2:I51;»Маты МП 50 мм»)​так​: Не работает​ документа​ надо что бы​ ну мол слабый,​ задано до 127​ «Месяц» было использовано​ в результате поставки​

СУММЕСЛИМН

​=SUMIF(A1:A5,»green»,B1:B5)​СЧЁТЕСЛИМН​ файл примера Лист​ Строке формул A2:A13=D2,​Найдем количество ящиков товара​​ временем дд.мм.гггг чч:мм,​​К сожалению, нет​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН($K$2:$K$51;$C$2:$C$51;A58;$I$2:$I$51;B58)+СУММЕСЛИМН($J$2:$J$51;$C$2:$C$51;A58;$H$2:$H$51;B58)​Имеет ли значение​

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

​ с определенным Фруктом​ столбец с цифрами​​ способа превращения массива​​или такКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(($C$2:$C$51=A58)*($H$2:$H$51=B58)*$J$2:$J$51+($C$2:$C$51=A58)*($I$2:$I$51=B58)*$K$2:$K$51)​

​ возможность отсутствия некоторых​

office-guru.ru>

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

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

Формулу используем следующую (но записываем ее, как формулу массива, то есть вводим ее через комбинацию клавиш CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;”Товар_1″;C2:C14;{“Китай”;”Грузия”}))

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

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

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

6

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

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

Функция ЕСЛИ в Excel (общие сведения)

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

1. Проверить всю колонку А на предмет наличия четных чисел.
2. Если четное число обнаружено, сложить такие-то значения.
3. Если четное число не обнаружено, то выдать надпись «не обнаружено».
4. Проверить получившееся число, является ли оно четным. 
5. Если да, то сложить его со всеми четными числами, отобранными в пункте 1.

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

Синтаксис функции ЕСЛИ с одним условием

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

=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)

Давайте рассмотрим синтаксис более подробно:

1. Логическое выражение. Это непосредственно условие, соответствие или несоответствие которому проверяет Excel. Проверяться могут как числовые, так и текстовая информация.
2. Значение_если_истина. Результат, который отобразится в ячейке в случае, если проверяемые данные соответствуют заданному критерию.
3. Значение_если_ложь. Результат, который выводится в ячейку, если проверяемые данные не соответствуют условию.

Вот пример для наглядности.

1

Здесь функция осуществляет сравнение ячейки А1 с числом 20. Это первый пункт синтаксиса. Если содержимое оказывается больше этого значения, в ячейку, где формула была прописана, выводится значение «больше 20». Если же этому условию ситуация не соответствует – «меньше или равно 20».

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

2

Поскольку нам нужно проверить текст, а не число, в первом аргументе указывается B2=”зач.”.

Синтаксис функции ЕСЛИ с несколькими условиями

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

Чтобы было более наглядно, вот синтаксис.

=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))

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

Вот пример.

3

А с помощью такой формулы (показана на скриншоте ниже) можно сделать анализ успеваемости каждого студента.

4

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

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

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

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

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

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

На сегодня все. Всех благ и до новых встреч на statanaliz.info.

Критерии

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

СУММЕСЛИМН и умные таблицы

Посмотрите на пример 1 нашего учебного файла, формула в L6

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

L4 и L5 содержат значения критериев.

Критерий для периода дат

А вот как задать период дат (с. по . ) при суммировании:

Обратите внимание на очень важную конструкцию:

Это не что иное, как выражение в виде текстовой строки. Причём это не статическое выражение, которое обычно приводят в справке по СУММЕСЛИМН , типа » >= 200000 «. Это выражение динамическое, то есть — гораздо более ценное и интересное. Всё что вам нужно — это вставить между знаком операции » >= » и ссылкой на ячейку с параметром — знак операции сложения строк » & «.

Динамическая операция сравнения

А почему бы не дать на откуп пользователю право определять операцию для критерия? В предыдущем примере пользователь мог выбирать порог N в критерии » >=N «. А в этом примере пользователь определяет и порог и саму операцию! А почему нет?

В L12 у нас находится выпадающий список, ссылающийся на 4 возможные операции: >, >=, ? » — заменяет любой символ, » * » — заменяет любое количество символов (в том числе и его отсутствие). Например критерий » *т* » сработает и на слово «Центр», и на слово «Восток». А, если бы существовала такая альтернатива, то сработал бы и на слово «опт» (это, как раз случай, когда вторая звёздочка в «*т*» заменила ноль символов справа от «т»).

Ответы на сложные вопросы

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

Как видите мы использовали 2 формулы СУММЕСЛИМН и 1 СРЗНАЧЕСЛИМН . СРЗНАЧЕСЛИМН вернула нам средние цены по холодильникам. Первая СУММЕСЛИМН вернула количество проданных телевизоров с ценами выше средних, а вторая формула вернула общее количество холодильников, проданных в Центре. Найдя частное между результатами двух СУММЕСЛИМН , мы получили долю от единицы и просто выразили её в процентах.

Набор магазинов

А что делать, если нам нужны продажи двух магазинов? В рамках одной формулы это решить в общем случае нельзя (метод через символы подстановки не универсален, так как магазины могут иметь полностью уникальные имена). Ответ очевиден — надо использовать 2 формулы и просто сложить их результаты. Хотя понятно, что такой метод подойдёт далеко не во всех ситуациях.

Но есть и ограничения.

Надеюсь вы убедились, что *ЕСЛИМН достаточно гибки, но есть один узкий момент, который надо хорошо понимать. Например, я хочу знать, сколько раз я торговал в Центре, предоставляя покупателям скидку более или равную 5%? Как я могу это узнать? Только вычисляя по каждой строке отношение предоставленной скидки к базовой цене. Понимаете — по каждой строке надо делать вычисление и сравнивать с 5%! Вот такое формулы *ЕСЛИМН сами, без вашей помощи сделать не смогут, так как формула один раз вычисляет критерий, а потом сравнивает его со всеми строками критериального диапазона, а нам надо это делать динамически. Но никто нам не мешает организовать дополнительный столбец, который будет предварительно считать отношение скидки к базовой цене, а после этого можно уже остальную работу поручить формуле СЧЁТЕСЛИМН (см. пример 7).

Пустые ячейки

Если вы хотите, чтобы формула *ЕСЛИМН отреагировала на пустые ячейки, то следует использовать критерии «» (пустая строка) или «=». Например, посмотрите лист Blank нашего учебного файла:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «=» )

однако, если пустота ячейки является следствием работы формулы (например, формулы ЕСЛИ ), то такая ячейка отреагирует только на критерий «», то есть:

Не пустые ячейки

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

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

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

Общий вид

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

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

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

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

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

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

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

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

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

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

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

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

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

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

​ условие, указав для​ можно оперативнее обеспечивать​ (например, содержащие «green»​Пример 7.​ воспользуйтесь функцией «СЧЕТЕСЛИ».​Пример 2.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН(J2:K51;C2:D51;»Орск»;H2:I51;»Маты МП 50 мм»)​так​ замечательной возможностью Excel​ Григорьева для «Копейки»),​

Синтаксис СУММЕСЛИМН и распространенные ошибки

​ для магазина «Копейка».​

​ клавишу TAB.​ формат, текстовый; «воспринимает»​ каждому менеджеру.​В строке «Диапазон​
​Суммирует продаж мяса в​ с функцией СУММЕСЛИМН,​ функции расположение данных​ вас актуальными справочными​ и больше 9),​Нужно посчитать продажи​ Подробнее об этой​Нужно посчитать, сколько​К сожалению, нет​
​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН($K$2:$K$51;$C$2:$C$51;A58;$I$2:$I$51;B58)+СУММЕСЛИМН($J$2:$J$51;$C$2:$C$51;A58;$H$2:$H$51;B58)​ как формулы массива,​ то функция​Если бы в нашей​В нашем примере диапазон​ математические операторы

Например,​Внимание!​ суммирования» пишем диапазон​Столбец C в​ то некоторые образцы​
​ (a2: a11), а​ материалами на вашем​ применяйте функцию​ Васечкиным по всем​ функции читайте в​ продано всех фруктов​ способа превращения массива​или такКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(($C$2:$C$51=A58)*($H$2:$H$51=B58)*$J$2:$J$51+($C$2:$C$51=A58)*($I$2:$I$51=B58)*$K$2:$K$51)​ то советую почитать​. ​СУММЕСЛИ (SUMIF)​ задаче было только​

​СУММЕСЛИ (SUMIF)​ задаче было только​

1. ​ суммирования – это​ 45; «​В условиях этой​ D2-D8 – это​ южном​ данных и формулы,​
2. ​ также какие условием​ языке. Эта страница​СЧЁТЕСЛИМН​ филиалам конкретного магазина.​ статье «Функция «СЧЕТЕСЛИ»​ на букву «Я».​ в диапазон, подходящий​
3. ​ZORRO2005​ предварительно про них​не поможет, т.к.​ одно условие (все​ диапазон ячеек с​Диапазон ячеек для проверки​ функции «СУММЕСЛИМН» количество​ столбец с суммами​

​область в столбце​ использующей функцию.​

• ​ является — наилучшим​ переведена автоматически, поэтому​(COUNTIFS).​ Таблица такая.​ в Excel».​
• ​ В условиях функции​ для таких функций​: или так:​

Примеры функции СУММЕСЛИМН в Excel

​ не умеет проверять​ заказы Петрова или​ количеством оказанных услуг.​ условия 2; условие​ ячеек диапазона суммирования​

​ покупок.​ A (результат —​Вы можете работать с​ образом «Южный». Обратите​

​ ее текст может​=СЧЁТЕСЛИМН(A1:A5;»green»;B1:B5;»>9″)​

​В условиях функции напишем​Пример 4.​ «СУММЕСЛИМН» напишем так.​ как СУММЕСЛИ, СУММЕСЛИМН…​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((C$2:C$51=»Орск»)*(H$2:I$51=»Маты МП 50 мм»)*J$2:K$51)​ Ну, а в​ больше одного критерия.​ все заказы в​ В качестве первого​ 2; … необязательные​ и количество ячеек​В строке «Диапазон​ 14 719).​ демонстрационными данными непосредственно​ внимание, запятые между​ содержать неточности и​

​=COUNTIFS(A1:A5,»green»,B1:B5,»>9″)​ так — «*Монетка*».​Функция «СУММЕСЛИМН» в​ «=я*» Вся формула​Но это уже​Perfect2You​ нашем случае задача​ Поэтому начиная с​ «Копейку», например), то​
​ аргумента выбираем столбец​ аргументы для назначения​ диапазона условий должно​ условия1» пишем диапазон​= SUMIFS(D2:D11,A2:A11,​ в этой книге​ отдельные аргументы:​ грамматические ошибки. Для​Для суммирования диапазона ячеек​ Формула получится такая.​Excel с датами​ будет такая
=СУММЕСЛИМН(D2:D8;C2:C8;»=я*»)​ другая тема​: Да, СУММЕСЛИМН работает​ решается одной формулой:​
​ версии Excel 2007​ задача решалась бы​ «Количество» (Е2:Е11). Название​ дополнительных диапазонов и​ быть одинаковое

Например,​ столбца с менеджерами.​«Южно» C2: C11,​ веб-приложения Excel Online.​
​= SUMIFS(D2:D11,A2:A11,»South»,​ нас важно, чтобы​ используйте функцию​

​=СУММЕСЛИМН(D2:D8;B2:B8;»Васечкин»;A2:A8;»*Монетка*»)​

​.​Этой формулой посчитаются​Belka​

​ только по одномерному​=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)​ в набор функций​ достаточно легко при​ столбца не нужно​ условий для них.​ все диапазоны с​

​ У нас -​ «Мясо»)​ Изменяйте значения и​Кавычки вокруг слова «Южный»​ эта статья была​СУММ​Последним условием мы​Нужно сложить данные​ все слова в​: Добрый день. Задача​ массиву (столбцу, либо​После ввода этой формулы​

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

​ Excel может проработать​ 3 по 8​

​ B2-B8.​Примечания:​ формулы или добавляйте​

exceltable.com>

Выборочные вычисления по одному или нескольким критериям

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

​Диапазон_условия2, Условие2, …​ «яблоки» или «32».​

​ офисов​​ Таким образом, поскольку​ продажи у менеджеров​ список для городов:​

Способ 1. Функция СУММЕСЛИ, когда одно условие

​ столбцов в диапазонах​ функции «СУММЕСЛИМН».​ B2-B8.​ содержимым этой ячейки​ критериев (например, «blue»​=СУММЕСЛИ(A2:A7;»»;C2:C7)​2 000 000 ₽​ (​ чисел в Excel.​Например, формула =СУММЕСЛИМН(A2:A9; B2:B9;​​=СУММЕСЛИМН(A2:A9; B2:B9; «Бананы»; C2:C9;​​    (необязательный аргумент)​​и все.. про различия​​2

это продажи​ мы перемножаем эти​ с фамилией из​​Теперь можно посмотреть, сколько​​ для проверки условий​Есть еще одна​​Обратите внимание​​ сцепили (&) знак​

​ и «green»), используйте​​Объем продаж всех продуктов,​​140 000 ₽​

• ​?​​Советы:​ «=Я*»; C2:C9; «Арте?»)​ «Артем»)​​Дополнительные диапазоны и условия​​ в условиях где-то​ за период с​ выражения, единица в​ пяти букв, можно​
• ​ услуг 2 оказано​​ не совпадает с​ функция в Excel,​.​ «*» (звездочка). Это​ функцию​ категория для которых​3 000 000 ₽​) и звездочку (​ ​ будет суммировать все​Суммирует количество продуктов, которые​ для них. Можно​ еще надо искать​ 01.02.2006 (это условие​ конечном счете получится​ использовать критерий​ в том или​ числом строк и​​ которая считает выборочно​​Когда мы поставили​ значит, что формула​СУММЕСЛИМН​ не указана.​210 000 ₽​*​​При необходимости условия можно​​ значения с именем,​ не являются бананами​
• ​ ввести до 127 пар​​Все имена заняты​ указано в ячейке​ только если оба​?????​ ином городе (а​

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

​ столбцов в диапазоне​ по условию. В​ курсор в эту​ будет искать в​(SUMIFS). Первый аргумент​​4 000 ₽​​4 000 000 ₽​). Вопросительный знак соответствует​ применить к одному​ начинающимся на «Арте»​ и которые были​ диапазонов и условий.​: СРЕДЗНАЧЕСЛИМН Подскажите как​​ b1) по 31.12.2011​​ условия выполняются. Теперь​. А чтобы найти все​ не только в​ для суммирования.​ ней можно указывать​ строку, автоматически появилась​​ столбце А все​​ – это диапазон​К началу страницы​280 000 ₽​

​ одному любому символу,​ диапазону, а просуммировать​ и оканчивающимся любой​ проданы продавцом по​Чтобы использовать эти примеры​​ записать формулу ссылаясь​​ (условие в ячейке​​ стоимости продаж осталось​​ продажи менеджеров, у​ Кемерово). Формулу немного​Бонусы при использовании функции​

​ разной длины диапазоны,​ новая строка для​ слова «Ашан» независимо​ для суммирования.​СЧЁТ​Формула​ а звездочка — любой​ соответствующие значения из​

Способ 3. Столбец-индикатор

​ буквой.​ имени Артем. С​ в Excel, выделите​ на ячейку правильно?​ b2)​ умножить на значения​ которых фамилия начинается​ видоизменим: =СУММЕСЛИМН($E$2:$E$11;$C$2:$C$11;F$2;$D$2:$D$11;$D$5).​ СУММЕСЛИМН:​ но условие можно​ условий. Если условий​ от того, что​=СУММЕСЛИМН(C1:C5;A1:A5;»blue»;B1:B5;»green»)​

​СЧЁТЕСЛИ​

​Описание​ последовательности символов. Если​ другого диапазона. Например,​Различия между функциями СУММЕСЛИ​ помощью оператора​ нужные данные в​Vlad999​с условием 1.​ получившегося столбца и​ на букву «П»,​Все диапазоны для суммирования​Возможность применения подстановочных знаков​ указать только одно.​ много, то появляется​ написано после слова​=SUMIFS(C1:C5,A1:A5,»blue»,B1:B5,»green»)​

Способ 4. Волшебная формула массива

​СЧЁТЕСЛИМН​Результат​ требуется найти непосредственно​ формула​ и СУММЕСЛИМН​<>​ таблице, щелкните их​: ни какой хитрости​ справилась ))​ просуммировать отобранное в​

​ а заканчивается на​

​ и проверки условий​ при задании аргументов.​ Подробнее о применении​ полоса прокрутки, с​​ «Ашан».​Примечание:​​СУММ​=СУММЕСЛИ(A2:A5;»>160000″;B2:B5)​ вопросительный знак (или​=СУММЕСЛИ(B2:B5; «Иван»; C2:C5)​Порядок аргументов в функциях​в аргументе​ правой кнопкой мыши​ это та же​а вот с​ зеленой ячейке:​ «В» — критерий​ нужно закрепить (кнопка​ Что позволяет пользователю​

Способ 4. Функция баз данных БДСУММ

​ функции «СУММЕСЛИ», о​​ помощью которой, переходим​​В Excel можно​​Аналогичным образом можно​​СУММЕСЛИ​Сумма комиссионных за имущество​ звездочку), необходимо поставить​суммирует только те​ СУММЕСЛИ и СУММЕСЛИМН​Условие1​ и выберите команду​ ф-ция СЦЕПИТЬ только​ диапазоном дат -​Если вы раньше не​П*В​ F4). Условие 1​

​ находить сходные, но​

planetaexcel.ru>