Как задать формулу суммы в excel с условием

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

Примеры формулы для суммы диапазонов с условием отбора в Excel

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

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

В приведенном примере сначала проверяется значения ячейки B2 и так как оно больше чем 0 – будет проигнорировано. Далее проверяется ячейка B3. В ней числовое значение меньше нуля, значит условие выполнено, поэтому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В результате его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а остальные ячейки не учитываются в итоговой сумме.

Обратите внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие между ними — это обратный оператор сравнения во втором аргументе где указывается условие для суммирования – вместо строки «<0» (меньше чем ноль) используется строка «>0» (больше чем ноль). Теперь мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.



Пример логического выражения в формуле для суммы с условием

Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно (<1000) придется использовать знак «меньше или равно» (<=1000) иначе цены со значением ровно 1000 не будут включены в расчеты. Более того в таком случае мы уже не можем использовать в другой формуле «больше или равно» (>=1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:

Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!

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

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

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

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

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

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

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

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

Создание критериев условий для функции СУММЕСЛИ

Второй аргумент функции называется «Критерий». Данный логический аргумент используется и в других подобных логических функциях: СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. В каждом случаи аргумент заполняется согласно одних и тех же правил составления логических условий. Другими словами, для всех этих функций второй аргумент с критерием условий является логическим выражением возвращающим результат ИСТИНА или ЛОЖЬ. Это значит, что выражение должно содержать оператор сравнения, например: больше (>) меньше (<) равно (=) неравно (<>), больше или равно (>=), меньше или равно (<=). За исключением можно не указывать оператор равно (=), если должно быть проверено точное совпадение значений.

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

Таблица правил составления критериев условий:

Во втором аргументе критериев условий можно использовать разные функции и формулы. Ниже на рисунке изображен список дат и присвоенных им значений. Важно отметить что сегодня на момент написания статьи дата – «03.11.2018». Чтобы суммировать числовые значения только по сегодняшней дате используйте формулу:

Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):

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

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

Суммируются все значения ячеек в диапазоне B2:B16 в соответствии со значениями в ячейках диапазона A2:A16, в которых после третьего символа фрагмент строки «-защита».

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

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

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

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

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

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

• Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
• Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как ">5". Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие "Иванов";
• Диапазон суммирования – массив значений, которые нужно сложить.

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

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

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

1. По свободной ячейке кликните левой клавишей мыши, чтобы выбрать место, где будет отображаться результат операции.
2. Кликните по иконке, расположенной рядом со строкой ввода формул, чтобы вызвать меню с функциями.
3. Выберите категорию «Математические» и найдите в списке функций «СУММЕСЛИ».
4. Кликните по кнопке «ОК», чтобы подтвердить свой выбор.
5. В появившемся окне «Аргументы функции» нужно заполнить обязательные поля: «Диапазон» и «Критерий». Например, нужно узнать, сколько килограмм фруктов продал продавец Иванов. Для этого выделите диапазон столбца «Продавец» полностью.
6. В строке «Критерий» пропишите фамилию продавца: «Иванов». Кавычки можно не указывать, программа вставляет их автоматически.
7. Поставьте курсор мыши в поле диапазона суммирования и выделите столбец «Количество (кг)» в таблице.
8. Нажмите на кнопку «ОК» внизу, чтобы произвести суммирование с условием, или из данной таблицы узнать, сколько килограмм фруктов продал Иванов.

В выделенной ячейке отобразится результат сложения, а в верхней строке – функция. Вычислить значение можно без вызова окна «Аргументы функции», если прописать все вручную. При этом базовая запись должна выглядеть так: =СУММЕСЛИ(X; «Условие»; Y), где X – это диапазон, а Y – диапазон суммирования. Значения не обязательно вписывать, их достаточно выделить с помощью мышки в таблице. Все элементы функции разделены знаком «;».

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

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

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

Чтобы суммировать ячейки с несколькими условиями, действуйте согласно следующей инструкции:

1. Выделите пустую ячейку, в которой будет отображаться конечный результат, затем нажмите на кнопку fx, которая находится рядом со строкой функций.
2. В разделе «Математические» в окне «Вставка функций» нажмите «СУММЕСЛИМН», затем подтвердите выбор, нажав на кнопку «ОК».
3. В появившемся окне в строке «Диапазон суммирования» введите ячейки, который находятся в столбце «Количество».
4. В «Диапазон условия» выделите все ячейки в столбце «Товар».
5. В качестве первого условия пропишите значение «Яблоки».
6. После этого необходимо задать второе условие и диапазон для него. В данной таблице столбец «Покупатели» является значением для диапазона. Выделите его в строку, затем в втором условии пропишите фамилию Евдокимов.
7. Нажмите на кнопку «ОК», чтобы программа посчитала, сколько яблок купил Евдокимов.

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

Итог

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

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

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

Имеем таблицу по продажам, например, следующего вида:

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

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

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig). Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

• Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
• Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и  ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий ?????. А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
• Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.

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

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

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

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.

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

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2=»Копейка»)*(B2=»Григорьев»)

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

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

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

=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)

После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

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

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM), которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:

=БДСУММ(A1:D26;D1;F1:G2)

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

​Смотрите также​​Если-бы вычисления производились​Если третий необязательный аргумент​Выполним расчет с помощью​ яблоки, яблоки местные​ преобразовав данные в​ сложно, Вы согласны?​ выше формулы в​ результату.​(LOOKUP) с функциями​Примечание​ Лист Текст и​ с функцией СУММЕСЛИМН,​ Для удобства также​ формуле указать адрес​55 000 ₽​ вопросительный знак (или​Примечание:​ в ячейках листа​ явно не указан,​ следующей формулы:​

​ и пр.) можно​​ таблицу, использовать имя​​Замечание.​ таблицу из предыдущего​Возможно, Вам стало любопытно,​СУММ​: для удобства, строки,​ Число):​ то некоторые образцы​ приводим ссылку на​ ячейки, в которой​Фрукты​​ звездочку), необходимо поставить​

​ Мы стараемся как можно​ (для наглядности я​ диапазон ячеек, указанных​

​Описание аргументов:​​ использовать критерии с​

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

• ​ всю работу формулы​ в качестве первого​A3:A22 – диапазон ячеек,​ подстановочным знаком *​

Синтаксис

​Main_table[ID]​

​ПРОСМОТР​​ выглядеть так:​​ рисунке выше отображает​

• ​СУММЕСЛИ​​ выделены Условным форматированием​Синтаксис функции:​ использующей функцию.​ языке) .​ даты. В ячейку​8 000 ₽​ «тильда» (​ актуальными справочными материалами​ продемонстрирую так, как-будто​ аргумента, также является​ содержащих порядковые номера​ (звездочка). Для этого​.​работала правильно, просматриваемый​

• ​В случае, когда Ваше​​[@Product]​(SUMIF). Примеры формул,​ с правилом =И($B2>=$D$2;$B2​СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)​Вы можете работать с​Допустим, вам нужно суммировать​ E10 устанавливаем функцию​Масло​~​ на вашем языке.​ вычисления происходят на​

  ​ диапазоном суммирования.​​ членов прогрессии, относительно​ нужно использовать конструкцию​criteria​ столбец должен быть​ искомое значение —​​, как искомое значение.​​ приведённые далее, помогут​Другой задачей может быть​B2:B13 Интервал_суммирования​

• ​ демонстрационными данными непосредственно​​ значения с более​ «СУММЕСЛИ».​4 000 ₽​).​ Эта страница переведена​​ листе, а не​​Условия, представленные в виде​​ которых задается критерий​​ *яблоки*.​(критерий) – так​ отсортирован в порядке​​ это массив, функция​​ Это происходит потому,​ Вам понять, как​ нахождение суммарных продаж​

• ​   — ячейки для​​ в этой книге​​ чем одного условия,​В ячейке B10​Овощи​​Функция СУММЕСЛИ возвращает неправильные​​ автоматически, поэтому ее​​ в памяти), то​​ текстовой строки или​ суммирования;​Решение задачи выглядит следующим​ как имена продавцов​ возрастания.​ВПР​ что мои данные​ эти функции работают​ за период (см.​​ суммирования, включающих имена,​​ веб-приложения Excel Online.​

Примечания

• ​ например сумму продаж​ пишем нужную дату.​Морковь​ результаты, если она​ текст может содержать​ массивы выглядели бы​

• ​ выражения, содержащего символы​​»​​ образом (учитываются значения​ записаны в просматриваемой​Функция​​становится бесполезной, поскольку​​ были преобразованы в​ и как их​ файл примера Лист​ массивы или ссылки,​ Изменяйте значения и​​ товара в определенной​​Диалоговое окно функции​42 000 ₽​ используется для сопоставления​ неточности и грамматические​​ так:​​ «>», «​

​Очевидно что если​​Если аргумент условие указан​​ содержащих значения членов​​ в любом месте​​ используем функцию​(SUMIF) в Excel​ работать с массивами​ команды​ данными.​

Примеры

Пример 1

​ другую исходную таблицу​ значения и текст​ свои собственные, чтобы​ обращения для использования​Нажимаем «ОК». Формула получилась​Яблоки​ или применяется к​ важно, чтобы эта​ например, D2=Карандаши, то​ в виде текстовой​ прогрессии.​ в диапазоне поиска):​ВПР​

Пример 2

​Insert​ пользователя, знакомого с​ продаж.​ …​Скопируйте все ячейки из​Просмотрите в этом примере,​Сумма ячеек​Описание​диапазон_суммирования​ уделить пару секунд​ D3=Папки, то ЛОЖЬ​ совпадение с указанной​ 15-ти значений (75%​ подстановочных знаков выглядит​, соответствующего заданному продавцу.​

​ сметаны по всем​

support.office.com

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

​Объем продаж всех продуктов​ аргументом​​ с помощью кнопок​ ​ нашем примере является​​ неточным совпадением, заменив​ данной геометрической прогрессии​Примеры, приведенные ниже, иллюстрируют​ для поиска используем​ лишь в том,​ВПР​
​ с простыми диапазонами.​​. Если Вам еще​ ​. Даты могут быть​
​ проверяется соответствующее условие.​ нового листа Excel.​
​ сумму продаж​
​ отделам за все​ ​ категории «Фрукты».​
​диапазон​ внизу страницы. Для​ значение Карандаши).​ недостающие символы звездочкой​ составляет всего лишь​ другие применения подстановочных​ формулу:​
​ что​ ​, к тому же​​ Например, когда Вы​ далеко до этого​ представлены в числовой​D2 «>=»&E2 Условие1; условие2;​ Вы можете отрегулировать​
​мяса​ дни. В ячейке​20 000 ₽​
​. При определении фактических​ удобства также приводим​Зная о том​ «*» (любое количество​ 0,41%.​ знаков.​VLOOKUP($F$2,Lookup_table,2,FALSE)​СУММЕСЛИ​
​ работает с массивами​ вводите формулу в​ уровня, рекомендуем уделить​ форме см. статью​ …​ ширину столбцов, чтобы​в​ В11 будем писать​=СУММЕСЛИ(A2:A7;»Овощи»;C2:C7)​ ячеек, подлежащих суммированию,​ ссылку на оригинал​ что значение ИСТИНА​ символов) или вопросительным​​Задача​ВПР($F$2;Lookup_table;2;ЛОЖЬ)​суммирует только те​
​ так же, как​ одну из ячеек,​ внимание первой части​ Как Excel хранит​   представляют собой от​ формулы лучше отображались.​южном​
​ название нужного продукта.​Объем продаж всех продуктов​
​ в качестве начальной​ (на английском языке).​
​ всегда равно 1,​ знаком «?» (один​Пример 2. В таблице​. Просуммировать значения, если​Конечно, Вы могли бы​ значения, которые удовлетворяют​ и с одиночными​ Excel автоматически копирует​ учебника – Функция​ дату и время​ 1 до 127​
​Регион​
​районе (из столбца​
​ Сейчас мы написали​ ​ категории «Овощи».​
​ используется верхняя левая​Функция​ а ЛОЖЬ всегда​ любой символ). В​ Excel указаны данные​ соответствующие ячейки:​ ввести имя как​ заданному Вами критерию.​ значениями.​ её на весь​ ВПР в Excel:​
​При необходимости даты могут​ условий в виде​
​Продавец​ A) (из столбца​​ «сметана». Получилось так.​120 000 ₽​
​ ячейка аргумента​
​СУММЕСЛИ​ ​ равно 0 мы​
​ качестве примеров могут​ о работе двух​Задача​ искомое значение напрямую​ Например, простейшая формула​Давайте разберем пример, чтобы​ столбец, что экономит​ синтаксис и примеры.​
​ быть введены непосредственно​ числа, выражения, ссылки​Что следует ввести​ C).​Эту функцию можно​=СУММЕСЛИ(B2:B7;»*ы»;C2:C7)​диапазон_суммирования​используется, если необходимо​
​ продолжаем работать с​ быть критерии «ст?л»​ продавцов небольшого магазина.​Критерий​ в функцию​ с​
​ Вам стало понятнее,​
​ несколько драгоценных секунд.​ ​ВПР и СУММ –​
​ в формулу =СУММЕСЛИМН(B6:B17;A6:A17;»>=15.01.2010″;A6:A17;»​ на ячейку или​Продажи​Вот формулу можно использовать​ использовать для заполнения​Объем продаж всех продуктов,​, а затем суммируются​ просуммировать значения диапазона,​ массивами как с​ (стол либо стул​ Определить, какой из​Формула​
​ВПР​СУММЕСЛИ​ о чём идет​Как видите, использовать функции​ суммируем все найденные​
​Чтобы вывести условия отбора​ ​ текста, определяющих, какие​​Южный​ для acomplish это:​ бланка, выбрав данные​ названия которых заканчиваются​ ячейки части диапазона,​ соответствующие указанному условию.​ числами 0 и​
​ при поиске наименований​ сотрудника принес больший​Результат​, но лучше использовать​:​ разговор. Предположим, у​ВПР​
​ совпадающие значения​ в текстовой строке​ ячейки будут просуммированы.​Орехов​=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat»)​ из большой таблицы.​ на «ы» («Помидоры»​ соответствующей по размерам​ Предположим, например, что​
​ 1.​ мебели), «Ива*» (фамилии​ доход за 19​Примечание​ абсолютную ссылку на​=SUMIF(A2:A10,»>10″)​ нас есть таблица,​и​Другие вычисления с ВПР​
​ используейте формулу =»Объем​Порядок аргументов различен в​Напитки​Результатом является значение 14,719.​ Смотрите статью «Как​ и «Апельсины»).​
​ аргументу​ в столбце с​Перемножив полученные значения​ Иванов, Иваненко, Иванищев​ рабочих дней).​заканчиваются на слово яблоки,​ ячейку, поскольку так​=СУММЕСЛИ(A2:A10;»>10″)​ в которой перечислены​СУММ​ (СРЗНАЧ, МАКС, МИН)​ продаж за период​

excel-office.ru

Суммирование значений с учетом нескольких условий

​ функциях СУММЕСЛИМН() и​​3571​Рассмотрим изучить все части​ посчитать в Excel​43 000 ₽​диапазон​ числами необходимо просуммировать​ массивов между собой​ и другие, которые​Исходная таблица имеет следующий​ например,​ мы создаём универсальную​– суммирует все значения​ имена клиентов, купленные​в Excel достаточно​ПРОСМОТР и СУММ –​ с «&ТЕКСТ(D6;»дд.ММ.гг»)&» по​ СУММЕСЛИ(). В СУММЕСЛИМН()​Западный​ формулы.​ ячейки в определенных​=СУММЕСЛИ(A2:A7;»»;C2:C7)​

​. Пример:​ только значения, превышающие​ последовательно, мы получим​ начинаются на «Ива»).​ вид:​Свежие яблоки​ формулу, которая будет​ ячеек в диапазоне​ товары и их​

​ просто. Однако, это​ поиск в массиве​ «&ТЕКСТ(E6;»дд.ММ.гг»)​ аргумент​Егоров​​= СУММЕСЛИМН является формулой​​ строках».​​Объем продаж всех продуктов,​​Диапазон​ 5. Для этого​ ОДИН массив из​

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

​*яблоки​

​ работать для любого​

​A2:A10​ количество (таблица Main​

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

​ категория для которых​

​Диапазон суммирования​ можно использовать следующую​ нолей и единиц.​ ячейки, содержащие коды​ в формуле:​=СУММЕСЛИ($A$3:$A$13; «*яблоки»;B3)​ значения, введённого в​​, которые больше​​ table). Кроме этого,​ решение, особенно, если​ значений​ Пользовательский формат.​является первым аргументом,​

​3338​ числа, которые находятся​ сделать самостоятельно любой​ не указана.​Фактические ячейки​ формулу:​ Там где выполнялись​ ошибок #ЗНАЧ! или​СУММЕСЛИ(C3:C21;»Петров»;B3:B21);»Иванов»;»Петров»)’ class=’formula’>​11​ эту ячейку.​10​

​ есть вторая таблица,​

​ приходится работать с​ВПР и СУММЕСЛИ –​Немного модифицируем условие предыдущей​

​ а в СУММЕСЛИ()​Восточный​ в этом случае​ бланк в Excel,​4 000 ₽​A1:A5​=СУММЕСЛИ(B2:B25;»>5″)​ все три критерия​ текстовые строки длиной​Функция ЕСЛИ выполняет проверку​​Использован подстановочный знак *​​sum_range​.​ содержащая цены товаров​

​ большими таблицами. Дело​

​ суммируем значения, удовлетворяющие​ задачи: найдем суммарные​ – третьим. При​Песоцкий​ в столбце D.​ смотрите в статье​​К началу страницы​​B1:B5​Это видео — часть​ отбора, (ИВАНОВ, КАРАНДАШИ,​ свыше 255 символов,​

​ возвращаемых значений функциями​ (перед значением)​(диапазон_суммирования) – это​Очень просто, правда? А​ (таблица Lookup table).​ в том, что​ определённому критерию​ продаж за месяц(см.​ копировании и редактировании​Напитки​ Первым делом нужно​ «Как сделать бланк​Рассмотрим одну из​B1:B5​​ учебного курса сложение​​ ЯНВАРЬ) т.е. все​

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

​ популярных​​A1:A5​​ чисел в Excel.​ условия принимали значения​

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

​ возвращать некорректный результат.​ проверки «Иванов» и​ например, ​ Так как данные​ немного более сложный​

​ написать формулу, которая​ может замедлить работу​ числовыми данными в​ Месяц).​ необходимо следить за​Северный​=СУММЕСЛИМН(D2:D11;​Четвертый пример.​функций в​

​B1:B3​Советы:​ ИСТИНА получаем 1​Аргументы могут ссылаться на​ «Петров» соответственно и​яблоки местные​ о продажах записаны​ пример. Предположим, что​

​C1:D4​​ диапазону, а просуммировать​

• ​ выполнялось — получим​ значений для такого​

• ​ оказалась больше.​Использован подстановочный знак *​, то мы просто​ и их номера​ВПР​ВПР​

support.office.com

Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL (Часть 2.Условие И)

​ другой таблицы, но​ месяца (предполагается, что​Разберем подробнее использование​Маринова​ условиям. Что диапазон​ и более столбцам,​ определенным условиям, которые​A1:B4​ соответствующие значения из​ 0 (1*1*0 =​

​ количества ячеек из​В итоге получим следующее​ (после значения)​ запишем​ID​, если искомое значение​. Получается, что чем​

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

​ и суммировать несколько​ в таблице данные​​ функции СУММПРОИЗВ():​​Сельхозпродукты​ ячеек — это​ то можно в​ мы пишем в​C1:C2​ другого диапазона. Например,​​ 0 ; 1*0*1​​ диапазона суммирования, которое​ значение:​начинаются с​​Main_table[Sales]​​(Lookup table). Кроме​ встречается несколько раз​ больше значений в​ столбцов или строк.​ в рамках 1​Результатом вычисления A2:A13=D2 является​8677​

​ первый​

​ формулу вставить несколько​​ формуле.​

• ​C1:D4​​ формула​ = 0 ;​ соответствует количеству ячеек,​Пример 3. В таблице​гру​.​
• ​ этого, есть ещё​ (это массив данных).​​ массиве, тем больше​ Для этого Вы​ года).​ массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение​
• ​Южный​аргумент​​ функций «СУММЕСЛИ». Например,​Разберем​Однако если аргументы​=СУММЕСЛИ(B2:B5; «Иван»; C2:C5)​ 0*1*1 = 0​ содержащихся в диапазоне.​ указаны данные о​

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

​ ).​
​ Расчет выполняется с​ зарплате сотрудника на​

• ​6​ сделать, это соединить​ которой те же​ комбинацию функций​​ рабочей книге и​​СУММ​ Выпадающего списка, перечень​ значения из столбца​Мясо​ — первая часть​​ из примера добавим​​Excel».​
• ​и​ значения из диапазона​Теперь осталось только​ левой верхней ячейки​​ протяжении 12 месяцев​​букв​
• ​ части в одно​ID​СУММ​ тем медленнее работает​и​ месяцев формируется с​А​450​
• ​ данных, функция требует​ столбец В с​У нас такая​диапазон_суммирования​ C2:C5, для которых​ умножить полученный массив​ диапазона суммирования.​
• ​ прошлого года. Рассчитать​гру???​ целое, и формула​связаны с данными​и​

​ Excel.​ВПР​ использованием Динамического диапазона​

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

​Функция СУММЕСЛИ позволяет использовать​ доходы работника за​=СУММЕСЛИ($A$3:$A$13; «гру???»;B3)​

​СУММЕСЛИ+ВПР​ о продажах (Main​ПРОСМОТР​Эту проблему можно преодолеть,​, как это показано​ (для исключения лишних​ персики. Массив можно​Егоров​ данных.​ в ячейке напишем​Первый пример.​ разное число ячеек,​​ диапазона B2:B5 равны​​ данные, которые нам​

​ только один критерий​​ весенние месяцы.​56​будет готова:​ table). Наша задача​

Задача2 (2 числовых критерия)

​:​ используя комбинацию функций​ ниже.​ месяцев).​ увидеть, выделив в​Мясо​После этого необходимо найти​ такую. =СУММЕСЛИ(A2:A7;B10;C2:C7)+СУММЕСЛИ(A2:A7;B10;D2:D7)​Сумма по условию​ пересчет листа может​

​ «Иван».​ необходимо в итоге​

​ суммирования. Если необходимо​​Вид таблицы данных:​Использован подстановочный знак ?​=SUMIF(Main_table[ID],VLOOKUP($F$2,Lookup_table,2,FALSE),Main_table[Sales])​ – найти сумму​

Задача3 (2 критерия Дата)

​=SUM(LOOKUP($C$2:$C$10,’Lookup table’!$A$2:$A$16,’Lookup table’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))​INDEX​Предположим, что у нас​Альтернативным вариантом для всех​ Строке формул A2:A13=D2,​7673​ данные, которые соответствуют​Этой формулой мы​Excel.​

​ занять больше времени,​Если необходимо выполнить суммирование​

​ просуммировать ( диапазон​​ указать сразу несколько​Весенними месяцами являются месяца​Учет РЕгиСТра приводит к​=СУММЕСЛИ(Main_table[ID];ВПР($F$2;Lookup_table;2;ЛОЖЬ);Main_table[Sales])​ продаж для заданного​

​=СУММ(ПРОСМОТР($C$2:$C$10;’Lookup table’!$A$2:$A$16;’Lookup table’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))​(ИНДЕКС) и​ есть список товаров​

​ 4-х задач является​ а затем нажав​Восточный​ двух условий, поэтому​ считаем выборочные данные​Нам нужно посчитать​

​ чем ожидалось.​ ячеек в соответствии​

Задача4 (Месяц)

​ F2:F16) и собственно,​ критериев, следует использовать​ с номерами 3,​ необходимости создания более​Урок подготовлен для Вас​

​ продавца. Здесь есть​Так как это формула​MATCH​ с данными о​ применение Автофильтра.​F9​Песоцкий​ введите свой первый​

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

Альтернативный вариант

​ 4 и 5.​ сложных формул. Чаще​ командой сайта office-guru.ru​

​ 2 отягчающих обстоятельства:​ массива, не забудьте​(ПОИСКПОЗ) вместо​ продажах за несколько​Для решения 3-й задачи​

​;​Сельхозпродукты​ условие, указав для​ и D по​ продал продукты первый​

excel2.ru

Как использовать функцию ВПР вместе с СУММ или СУММЕСЛИ в Excel

​ следующей таблицы и​ используйте функцию СУММЕСЛИМН.​ на 0 не​Критерий суммирования не обязательно​​ Для расчета используем​​ всего используются формулы​​Источник: https://www.ablebits.com/office-addins-blog/2014/08/05/excel-vlookup-sum-sumif/​​Основная таблица (Main table)​​ нажать комбинацию​​VLOOKUP​ месяцев, с отдельным​ таблица с настроенным​Результатом вычисления B2:B13 является​664​

​ функции расположение данных​ первому отделу.​ отдел за все​ вставьте их в​СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])​ умножилось.​ должен относиться к​ формулу:​ на основе функций​Перевел: Антон Андронов​ содержит множество записей​Ctrl+Shift+Enter​(ВПР) и​ столбцом для каждого​ автофильтром выглядит так​ массив {3:5:11:98:4:8:56:2:4:6:10:11}, т.е.​Северный​ (a2: a11), а​Функцией «СУММЕСЛИ» в​ дни. В ячейке​ ячейку A1 нового​Аргументы функции​

​Теперь сравните полученные​ диапазону суммирования. Например,​2″;B3:B14)-СУММЕСЛИ(A3:A14;»>5″;B3:B14)’ class=’formula’>​ НАЙТИ() и СОВПАД()​Автор: Антон Андронов​ для одного​при завершении ввода.​SUM​ месяца. Источник данных​ (см. файл примера​ просто значения из​Егоров​ также какие условием​Excel​ С9 устанавливаем функцию​ листа Excel. Чтобы​

​СУММЕСЛИ​ при помощи формулы​ для расчета общей​Сумма зарплат с 6-го​ учитывающих регистр.​Для суммирования значений по​ID​​Lookup table​​(СУММ). Далее в​​ – лист​​ Лист 2 Даты).​​ столбца​​Сельхозпродукты​​ является — наилучшим​​можно посчитать, только,​ «СУММЕСЛИ».​ отобразить результаты формул,​описаны ниже.​ и при пошаговом​ зарплаты сотрудника за​ по 12-й месяц​

​Ниже приведены формулы для​ одному диапазону на​в случайном порядке.​– это название​ этой статье Вы​​Monthly Sales​​Предварительно таблицу нужно преобразовать​B​1500​ образом «Южный». Обратите​ отрицательные или, только,​В​ выделите их и​

• ​Диапазон​ вычислении на листе​ год в таблице,​
• ​ является подмножеством множества​ суммирования чисел, если​
• ​ основе данных другого​Вы не можете добавить​ листа, где находится​ увидите несколько примеров​
• ​:​ в формат таблиц​;​

ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений

​Южный​ внимание, запятые между​ положительные числа. Об​Excel функция «СУММЕСЛИ»​ нажмите клавишу F2,​   . Обязательный аргумент. Диапазон​ массивы (выделены красным).​ в которой содержатся​ суммы зарплат с​ соответствующие значения совпадают​ диапазона используется функция​​ столбец с именами​​ просматриваемый диапазон.​​ таких формул.​​Теперь нам необходимо сделать​ MS EXCEL 2007​

​Результатом поэлементного умножения массивов​Маринова​ отдельные аргументы:​ этом способе, читайте​находится на закладке​ а затем — клавишу​ ячеек, оцениваемых на​Думаю всё понятно​​ данные о зарплате​​ 3-го по 12-й​

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

​Только что мы разобрали​ таблицу итогов с​ и включить строку​​ (A2:A13=D2)*(B2:B13) является {0:0:0:0:4:8:56:0:0:0:0:0}.​​Мясо​​= SUMIFS(D2:D11,A2:A11,»South»,​​ в статье «Сумма​

​ «Формулы» в разделе​ ВВОД. При необходимости​
​ соответствие условиям. Ячейки​

​ :)​ всех сотрудников, можно​​ месяц. Разница этих​​ учетом регистра.​ когда критерий применяется​ таблице.​ формулы, чтобы Вы​​ пример, как можно​​ суммами продаж по​ Итогов.​​ При умножении числа​​6596​​Кавычки вокруг слова «Южный»​​ отрицательных чисел в​​ «Библиотека функций». Нажимаем​​ измените ширину столбцов,​

​ в каждом диапазоне​МИНУСЫ: СУММПРОИЗВ -​​ ввести формулу =СУММЕСЛИ(A1:A100;”Петренко”;B1:B100),​​ сумм является искомой​​Просуммировать значения, если соответствующие​​ к диапазону содержащему​Давайте запишем формулу, которая​ понимали, как она​ извлечь значения из​ каждому товару.​​В этом уроке Вы​​ на значение ЛОЖЬ​​Формула​​ укажите, что это​

​ Excel».​
​ на «Математические» и​

​ чтобы видеть все​​ должны содержать числа,​ «тяжёлая» формула массива.​ где:​​ величиной – суммой​​ ячейки:​​ текстовые значения.​​ найдет все продажи,​ работает, и могли​ нескольких столбцов таблицы​

​Решение этой задачи –​
​ найдёте несколько интересных​

​ получается 0; а​Описание​​ текстовые данные.​​Чтобы сложить и​ выбираем функцию «СУММЕСЛИ».​ данные.​ имена, массивы или​ При вычислениях на​

​a. A1:A100 – диапазон​ зарплат за весенние​Критерий​​Пусть дана таблица с​​ сделанные заданным продавцом,​ настроить её под​ и вычислить их​ использовать массив констант​ примеров, демонстрирующих как​ на значение ИСТИНА​​Результат​​Наконец, введите аргументы для​​ удалить дубли в​​Появится диалоговое окно​Стоимость имущества​ ссылки на числа.​ больших диапазонах данных​ ячеек, в которых​ месяцы:​Формула​ перечнем наименований фруктов​ а также просуммирует​ свои нужды. Функцию​ сумму. Таким же​

​ в аргументе​​ использовать функцию​​ (=1) получается само​​’ = СУММЕСЛИМН (a2:​​ второго условия –​ таблице, тоже применяем​ «Аргументы функции». Заполняем​Комиссионные​ Пустые ячейки и​ заметно увеличивается время​ хранятся фамилии сотрудников;​Функцию СУММЕСЛИ можно использовать​Результат​ и их количеством​ найденные значения.​СУММ​ образом Вы можете​​col_index_num​​ВПР​ число;​ a11; D2: D11​ диапазон ячеек (C2:​ функцию «СУММЕСЛИ». Смотрите​ его так.​Данные​

​ ячейки, содержащие текстовые​ пересчёта.ПРИМЕЧАНИЯ: Количество обрабатываемых​​b. «Петренко» – критерий​​ если требуется определить​​Примечание​​ (см. файл примера).​​Перед тем, как мы​​пока оставим в​​ выполнить другие математические​​(номер_столбца) функции​(VLOOKUP) вместе с​Разберем второе условие: Результатом​«Южно» C2: C11,​

Выполняем другие вычисления, используя функцию ВПР в Excel

​ C11), содержащий слово​ статью «Как сложить​В строке «Диапазон»​1 000 000 ₽​ значения, пропускаются. Выбранный​ массивов ограничено 255.​ поиска (фамилия работника)​ сразу несколько значений​в точности равны Яблоки​Если в качестве диапазона,​​ начнём, позвольте напомнить​​ стороне, так как​ операции с результатами,​

Вычисляем среднее:

​ВПР​
​СУММ​

​ вычисления —(B2:B13>=E2) является​ «Мясо»)​ «Мясо», плюс само​​ и удалить дубли​​ указываем диапазон первого​70 000 ₽​ диапазон может содержать​ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая​ для диапазона A1:A100;​ для различных критериев.​

Находим максимум:

​ с учетом регистра​
​ к которому применяется​

​ Вам синтаксис функции​ её цель очевидна.​ которые возвращает функция​​. Вот пример формулы:​​(SUM) или​ массив {0:0:1:1:0:1:1:0:0:1:1:1}. Значения в столбце​Суммирует продаж мяса в​ слово (заключено в​ в Excel».​ столбца, с номерами​

Находим минимум:

​2 500 000 ₽​
​ даты в стандартном​

​ версия Excel​c. B1:B100 – диапазон​ Например, для расчета​​Яблоки​​ критерий, выступает диапазон​СУММЕСЛИ​LOOKUP($C$2:$C$10,’Lookup table’!$A$2:$A$16,’Lookup table’!$B$2:$B$16)​ВПР​=SUM(VLOOKUP(lookup value, lookup range,​СУММЕСЛИ​

Вычисляем % от суммы:

​ «Количество ящиков на​
​Столбец C в​

​ кавычки) таким образом,​Ещё примеры этой​ отделов, п. ч.​​2 000 000 ₽​​ формате Excel (см.​Способ 3: Формула​ ячеек, в которых​ суммы зарплат за​=СУММ(ЕСЛИ(СОВПАД( «Яблоки»;A3:A13);1;0)*B3:B13)​ с текстовыми значениями,​(SUMIF):​ПРОСМОТР($C$2:$C$10;’Lookup table’!$A$2:$A$16;’Lookup table’!$B$2:$B$16)​

​. Вот несколько примеров​ {2,3,4}, FALSE))​(SUMIF) в Excel,​ складе», которые удовлетворяют​ южном​

ПРОСМОТР и СУММ – поиск в массиве и сумма связанных значений

​ Excel может соответствовать​ функции смотрите в​ мы хотим посчитать​​140 000 ₽​​ примеры ниже).​ массива​ хранятся данные о​ три первых и​4​ то можно рассмотреть​​SUMIF(range,criteria,[sum_range])​​Функция​ формул:​​=СУМ(ВПР(искомое_значение;таблица;{2;3;4};ЛОЖЬ))​​ чтобы выполнять поиск​ критерию >=E2 (т.е.​область в столбце​ его. Завершить формулу​ статье «Функция «СУММЕСЛИ»​

​ данные только по​3 000 000 ₽​Условие​Code =СУММ(ЕСЛИ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2);F2:F16))​ зарплатах работников (диапазон​ три последних месяца​использована формула массива​ несколько типов задач​СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])​ПРОСМОТР​{=AVERAGE(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​Как видите, мы использовали​ и суммирование значений​ >=6) соответствуют 1;​ A (результат —​ с закрывающую скобку​ в Excel, примеры»​

​ первому отделу. Мы​210 000 ₽​​   . Обязательный аргумент. Условие​​В английской версии:​ суммирования).​ в году соответственно​содержат значение Яблоки в​ суммирования:​​range​​просматривает товары, перечисленные​​{=СРЗНАЧ(ВПР(A2;’Lookup Table’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​​ массив​

​ по одному или​
​Далее, функция СУММПРОИЗВ() попарно​

​ 14 719).​)​ здесь. В этой​​ указали столбец A.​​4 000 000 ₽​

​ в форме числа,​​Code =SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))​Предположим что у​ составим следующую таблицу:​

​ любом месте текстовой​суммирование значений, если соответствующие​(диапазон) – аргумент​ в столбце C​Формула ищет значение из​{2,3,4}​​ нескольким критериям.​​ перемножает элементы массивов​= SUMIFS(D2:D11,A2:A11,​и нажмите клавишу​

1. ​ статье мы рассмотрели,​
​В строке «Критерий»​

   ​280 000 ₽​​ выражения, ссылки на​​КАК ЭТО РАБОТАЕТ:​ Вас есть вот​Для расчетов используем следующую​ строки с учетом​ им ячейки в​ говорит сам за​ основной таблицы (Main​

2. ​ ячейки A2 на​​для третьего аргумента,​​Вы пытаетесь создать файл-сводку​ и суммирует полученные​«Южно» C2: C11,​ ВВОД. Результат, снова​ как использовать символы,​ указываем, какой отдел​Формула​ ячейку, текста или​​ Точно так же​​ такой отчёт по​ формулу:​
3. ​ регистра ​​ диапазоне поиска соответствуют​​ себя. Это просто​ table), и возвращает​ листе​ чтобы выполнить поиск​ в Excel, который​ произведения. Получаем –​ «Мясо»)​​ нажмите — 14,719.​​ для поиска конкретных​​ из столбца А​​Описание​ функции, определяющее, какие​ как и Способ​ продажам торговых представителей:​В результате получим:​Яблоки​ критерию (простейший случай);​

​ диапазон ячеек, которые​ соответствующую цену из​Lookup table​ несколько раз в​ определит все экземпляры​ 64.​Примечания:​​=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat»)​​ данных.​ нам нужно посчитать.​Результат​ ячейки необходимо суммировать.​

​ №2. Есть только​​Из него Вам​​Функция СУММЕСЛИ имеет следующий​​=СУММ(ЕСЛИ( СОВПАД(«Яблоки»;A3:A13);1;0) *B3:B13)​в критерии применяются подстановочные​ Вы хотите оценить​ столбца B просматриваемой​

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию

​и вычисляет среднее​​ одной функции​​ одного конкретного значения​3. Другим вариантом использования​​ ​​По мере ввода функции​Если нужно указать​ Здесь можно указать​=СУММЕСЛИ(A2:A5;»>160000″;B2:B5)​ Например, условие может​ два отличия -​​ необходимо узнать сколько​​ синтаксис:​15​ знаки (*, ?);​ заданным критерием.​ таблицы (Lookup table).​​ арифметическое значений, которые​​ВПР​

​ и просуммирует другие​
​ функции СУММПРОИЗВ() является​

​Дополнительные примеры можно найти​ СУММЕСЛИМН в Microsoft​​ не одно условие​​ не только номер​​Сумма комиссионных за имущество​​ быть представлено в​

​ данная формула вводится​карандашей​=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])​использована формула массива​критерий сравнивается со значениями​criteria​$​ находятся на пересечении​​, и получить сумму​​ значения, связанные с​ формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)).​ в статьеФункция СУММЕСЛИМН.​ Excel, если вы​​ суммирования, а несколько,​​ отдела (например, 1),​ стоимостью больше 1​ таком виде: 32,​ нажатием Ctrl+Shift+Enter, а​продал торговый представитель​Описание аргументов:​СОВЕТ:​

• ​ в диапазоне поиска​(критерий) – условие,​D$2:$D$10​​ найденной строки и​​ значений в столбцах​
• ​ ним? Или Вам​4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2))​Если вы хотите подсчитать​ не помните аргументы​

​ то применяем функцию​ но и текст,​ 600 000 ₽.​ «>32», B5, «32»,​ не просто нажатием​

​Иванов​диапазон – обязательный для​Для сложения с​​ с учетом РЕгиСтРА.​​ которое говорит формуле,​

​– количество товаров,​
​ столбцов B, C​

• ​2​​ нужно найти все​ похожа на вышеупомянутую​ итоговое значение только​ справки готов под​ «СУММЕСЛИМН». Про эту​ символы, адрес отдельной​
• ​630 000 ₽​​ «яблоки» или СЕГОДНЯ().​ Enter и массив​в​ заполнения аргумент, принимающий​ несколькими критериями воспользуйтесь​Рассмотрим эти задачи подробнее.​ какие значения суммировать.​
• ​ приобретенных каждым покупателем,​​ и D.​,​ значения в массиве,​ формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После​ для одного диапазона​ рукой. После ввода​ функции читайте в​ ячейки с номером​=СУММЕСЛИ(A2:A5; «>160000»)​Важно:​

​ 0-й и 1-ц​январе​ ссылку на диапазон​​ статьей Функция СУММЕСЛИМН()​​Найдем количество всех значений​ Может быть числом,​ чьё имя есть​{=MAX(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​3​ удовлетворяющие заданному условию,​ ее ввода нужно​

1. ​ с учетом значения​​= СУММЕСЛИМН (​ статье «Суммирование ячеек​ отдела, код, т.​​Сумма по имуществу стоимостью​​ Все текстовые условия и​ не умножается на​.​ ячеек с данными,​ Сложение с несколькими​ «Яблоки», т.е. просуммируем​​ ссылкой на ячейку,​​ в столбце D​{=МАКС(ВПР(A2;’Lookup Table’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​и​ а затем просуммировать​​ вместо​​ в другом диапазоне,​
2. ​, автозавершение формул появится​​ в Excel по​ д. Какие символы​ больше 1 600 000​ условия с логическими​ диапазон суммирования, а​​ПРОБЛЕМА​​ в отношении которых​​ критериями в MS​​ значения из столбца​ выражением или другой​ основной таблицы. Умножая​Формула ищет значение из​4​

   ​ связанные значения с​
​ENTER​

   ​ используйте функцию СУММЕСЛИМН.​ под формулы, в​ условию» тут.​ можно использовать в​​ ₽.​​ и математическими знаками​ отбирается с помощью​: Как суммировать данные​ применяется определенный критерий.​ EXCEL (Часть 2.Условие​ Количество, для которых​ функцией Excel.​ количество товара на​

3. ​ ячейки A2 на​​.​ другого листа? Или,​нажать​Произведем сложение значений находящихся​ списке аргументов в​Какие ещё, есть​​ формуле, что они​​9 000 000 ₽​ необходимо заключать в​​ функции ЕСЛИ.​​ по нескольким критериям??​

​ В ячейках данного​ И).​ соответствующее значение из​sum_range​​ цену, которую возвратила​​ листе​

​Теперь давайте применим эту​
​ может быть, перед​

​CTRL+SHIFT+ENTER​ в строках, поля​
​ их правильном порядке.​
​ способы суммирования в​

​ означают, смотрите в​

office-guru.ru

Сложение с одним критерием в MS EXCEL (Часть2.ТЕКСТовый критерий)

​=СУММЕСЛИ(A2:A5;300000;B2:B5)​ двойные кавычки (​МИНУСЫ: Формулы массива​РЕШЕНИЕ​ диапазона могут содержаться​В статье Сложение по​ столбца Фрукты в​(диапазон_суммирования) – необязательный,​

​ функция​Lookup table​ комбинацию​ Вами встала ещё​

​5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет​ которых удовлетворяют сразу​Если посмотреть на изображение​ Excel, не обязательно​ статье «Символы в​Сумма комиссионных за имущество​»​

• ​ при вычислениях на​: Способ 1:​ имена, текстовые строки,​ условию (один Числовой​
• ​ точности равно «Яблоки»​ но очень важный​
• ​ПРОСМОТР​и возвращает максимальное​ВПР​

​ более трудная задача,​

Значение соответствует критерию

​ еще один вариант​ двум критериям (Условие​ использования функции автозавершения​ формулами, смотрите в​ формулах Excel» тут.​ стоимостью 3 000 000​). Если условием является​ больших диапазонах данных​

​Code =БДСУММ(A1:G16;F1;I1:K2)​
​ данные ссылочного типа,​

​ критерий) рассмотрен случай,​ (без учета РЕГИСТРА).​​ для нас аргумент.​​, получаем стоимость каждого​ из значений, которые​
​и​

​ например, просмотреть таблицу​ многокритериального подсчета значений.​ И). Рассмотрим Текстовые​ формул и списка​ статье «Суммирование в​
​В строке «Диапазон​

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

​ ₽.​ число, использовать кавычки​ заметно увеличивают время​В английской версии:​ числовые значения, логические​ когда критерий применяется к​

​Для подсчета используем формулу​ Он определяет диапазон​ приобретенного продукта.​ находятся на пересечении​СУММ​ всех счетов-фактур Вашей​6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует​ критерии, Числовые и​ аргументов, то в​ Excel».​ суммирования» мы указываем​210 000 ₽​ не требуется.​ пересчёта.ПРИМЕЧАНИЯ: Количество обрабатываемых​Code =DSUM(A1:G16,F1,I1:K2)​ ИСТИНА или ЛОЖЬ,​ числовым значениям из​=СУММЕСЛИ(A3:A13;»яблоки»;B3:B13)​

​ связанных ячеек, которые​$B$2:$B$10=$​ найденной строки и​к данным в​ компании, найти среди​
​ предварительного создания таблицы​

​ критерии в формате​ нашем примере диапазон_суммирования​При написании формулы,​
​ диапазон столбца с​

​=СУММЕСЛИ(A2:A5;»>» &C2;B2:B5)​Диапазон_суммирования​ массивов ограничено 255.​

​КАК ЭТО РАБОТАЕТ:​​ даты в формате​ диапазона, по которому​

Критерий сравнивается со значениями в диапазоне поиска с учетом РЕгиСТРА

​ версия Excel​ базы данных A1:G16​ диапазон также является​Функция СУММЕСЛИ в Excel​D5​ Excel суммирует значения​ имена клиентов в​

​{=MIN(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​ продаж в столбцах​ их?​ БДСУММ()). Заголовки этой​ SUMIFS().​

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

​ таблицы с именем​Формула ищет значение из​B​ их смысл одинаков​ точности совпадать с​ возьмем таблицу с​ просуммировать; диапазон_условия1 —​

excel2.ru

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

​ что они означают,​Заполнили диалоговое окно​490 000 ₽​ указанных в качестве​В английской версии:​ столбца Количество (аргумент​ итоговый результат не​ содержащихся в диапазоне​=СУММЕСЛИ(A3:A13;D5;B3:B13)​Собрав все воедино, давайте​ в ячейке G1.​ ячейки A2 на​по​ – необходимо найти​ соответствующими заголовками исходной​ двумя столбцами (полями):​ это диапазон A2.A11,​ как их найти​

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

​ так.​Скопируйте образец данных из​диапазона​Code =SUMIFS(F2:F16,B2:B16,I2,D2:D16,J2,A2:A16,K2)​ «Поле» = F1)​ влияют пустые ячейки​ ячеек, с учетом​В качестве диапазона суммирования​

​ определим третий аргумент​

​ Если есть совпадение,​ листе​

​M​

• ​ и просуммировать значения​ таблицы. Размещение условий​ текстовым «Фрукты» и​ представляющий собой столбец​ и исправить? Чтобы​
• ​Нажимаем «ОК». Получилась такая​
• ​ приведенной ниже таблицы​. Если аргумент​КАК ЭТО РАБОТАЕТ:​

​ по заданным в​

​ и ячейки, содержащие​ критерия, указанного в​ можно указать лишь​ для нашей функции​ возвращается​Lookup table​:​

​ по одному или​

Сумма ячеек с определенным значением в Excel

​ в одной строке​ числовым «Количество на​ с данными, в​ ответить на эти​ формула. =СУММЕСЛИ(A2:A7;1;C2:C7)​ и вставьте его​диапазон_суммирования​Функция СУММЕСЛИМН требует​

​ ячейках I1:K2 (Продавец​ текстовые данные.​

​ качестве одного из​ первую ячейку диапазона​

​СУММЕСЛИ​

​1​и возвращает минимальное​=SUM(VLOOKUP(B2,’Monthly sales’!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))​ нескольким критериям в​ соответствует Условию И​ складе» (См. файл​ котором находится​ вопросы, читайте статью​Мы рассмотрели этот​

​ в ячейку A1​опущен, Excel суммирует​

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

​ обязательно указать диапазон​ = Иванов; Продукция​условие – обязательный для​ аргументов, и возвращает​ — функция СУММЕСЛИ()​. Как Вы помните,​, в противном случае​

​ из значений, которые​

​=СУМ(ВПР(B2;’Monthly sales’! $A$2:$M$9;{2;3;4;5;6;7;8;9;10;11;12;13};ЛОЖЬ))​ Excel. Что это​ (см. диапазон​ примера).​условие1​

​ «Ошибки в формулах​

​ вариант установки формулы​ нового листа Excel.​ ячейки, указанные в​ суммирования (в нашем​ = Карандаши; Месяц​ заполнения аргумент, который​ соответствующее числовое значение.​ просуммирует все правильно:​ мы хотим суммировать​0​

​ находятся на пересечении​Важно!​ за значения? Любые​D14:E15​Найдем количество ящиков товара​— “Южный”.​ Excel».​ в ячейку, чтобы​ Чтобы отобразить результаты​ аргументе​

​ случае F2:F16) и​ = Январь) критериям.​

​ может быть указан​

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

​ Данная функция является​=СУММЕСЛИ(A3:A13;D5;B3)​

​ все продажи, совершённые​

​. Таким образом, отбрасываются​

• ​ найденной строки и​Если Вы вводите​ числовые. Что это​).​ с определенным Фруктом​По мере того, как​Примечание:​ понять принцип работы​ формул, выделите их​диапазон​ хотя бы одну​МИНУСЫ: Список критериев​ в виде числа,​ альтернативой совместного использования​Просуммируем значения из столбца​ определённым продавцом, чьё​ имена покупателей, отличающиеся​ столбцов B, C​ формулу массива, то​ за критерии? Любые…​Примечание​
• ​И​ вы вводите формулу,​Мы стараемся как​ функции «СУММЕСЛИ». Когда​ и нажмите клавишу​(те же ячейки,​ пару Диапазон/Условие.​ должен быть на​ текстовой строки, логического​ функций СУММ и​ Количество, для которых​
• ​ имя задано в​ от указанного в​ и D.​ обязательно нажмите комбинацию​ Начиная с числа​: для удобства, строки,​, у которых Остаток​ в автозавершении формулы​

​ можно оперативнее обеспечивать​

1. ​ поймем принцип составления​ F2, а затем —​ к которым применяется​По сути эта​ листе.ПРИМЕЧАНИЯ: Количество критериев​ выражения, результата выполнения​
2. ​ ЕСЛИ. Ее использование​ соответствующее значение из​ ячейке F2 (смотрите​ ячейке G1, ведь​
3. ​{=0.3*SUM(VLOOKUP(A2,’Lookup Table’!$A$2:$D$10,{2,3,4},FALSE))}​Ctrl+Shift+Enter​ или ссылки на​ участвующие в суммировании,​ ящиков на складе​ появятся остальные аргументы​ вас актуальными справочными​ формулы с функцией​ клавишу ВВОД. Кроме​ условие).​ функция «один в​ суммирования ограничено оперативной​ какой-либо функции. Переданное​ позволяет упростить формулы,​ столбца Фрукты содержит​ рисунок, приведённый выше).​ все мы знаем​{=0.3*СУММ(ВПР(A2;’Lookup Table’!$A$2:$D$10;{2;3;4};ЛОЖЬ))}​вместо обычного нажатия​ ячейку, содержащую нужное​ выделены Условным форматированием​
4. ​ не менее минимального.​ (здесь они не​ материалами на вашем​ «СУММЕСЛИ», можно формулу​ того, вы можете​В аргументе​ один» повторяет действие​
5. ​ памятью.​ в качестве данного​ поскольку критерий, по​ слово Яблоки (без​range​ – умножение на​Формула ищет значение из​Enter​ значение, и заканчивая​ с правилом =И($A2=$D$2;$B2>=$E$2)​ Например, количество ящиков​ показаны);​
6. ​ языке. Эта страница​ написать, сразу, в​ настроить ширину столбцов​условие​ функции СУММПРОИЗВ в​ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая​
7. ​ аргумента значение или​ которому производится суммирование​ учета РЕгиСТРА).​(диапазон) – так​ ноль дает ноль.​ ячейки A2 на​. Microsoft Excel заключит​ логическими операторами и​Другой задачей может быть​ с товаром персики​диапазон_условия2​

• ​ переведена автоматически, поэтому​ ячейке, не вызывая​ в соответствии с​
• ​можно использовать подстановочные​ нашем примере, но​ версия Excel​
• ​ выражение является критерием​ значений, предусмотрен непосредственно​Для решения этой задачи​ как мы ищем​Так как наша формула​

exceltable.com

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

​ листе​ Вашу формулу в​ результатами формул Excel.​ нахождение сумм ящиков​

​ (ячейка​— это диапазон​​ ее текст может​​ окно функции.​​ содержащимися в них​​ знаки: вопросительный знак​​ не является формулой​​Способ 2:​

​ суммирования рассматриваемой функции.​​ в ее синтаксисе.​ используем подстановочные знаки (*,​
​ по​​ – это формула​
​Lookup table​
​ фигурные скобки:​
​Итак, есть ли в​
​ только тех партий​

​D2​ C2:C11, представляющий собой​ содержать неточности и​Получились такие данные.​ данными.​ (​ массива.​Code =СУММПРОИЗВ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2)*F2:F16)​[диапазон_суммирования] – необязательный для​Пример 1. В таблице​ ?). Подход заключается​

​ID​ массива, она повторяет​, затем суммирует значения,​{=SUM(VLOOKUP(B2,’Monthly sales’!$A$2:$M$9,{2,3,4,5,6,7,8,9,10,11,12,13},FALSE))}​ Microsoft Excel функционал,​

​ кнопок внизу страницы.​

Помогла ли Вам статья?