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 символа от начала строки. Нет необходимости использовать сложные формулы с функцией =ЛЕВСИМВ() и т.д. Достаточно лишь воспользоваться операторами многозначных символов чтобы сформулировать простой и лаконичный запрос к базе данных с минимальными нагрузками на системные ресурсы.

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

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

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

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

Способ 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)

Ячейка начинается с определенного значения

=СУММЕСЛИ(диапазон;»текст*»;ранг-сумм)

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

В примере показано, что ячейка G5 содержит эту формулу:

=СУММЕСЛИ(C5:C11; «футболка*»; D5:D11)

Эта формула суммирует суммы в столбце D, когда значение в столбце C начинается с «футболка». Обратите внимание, что СУММЕСЛИ нечувствительна к регистру.

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.

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

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

=СУММЕСЛИМН(Д5:Д11;С5:С11;»футболка*»)

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

Сумма значений между определенными числами

=СУММЕСЛИМН(ранг-сумм;ранг_критериев;»>500″;ранг_критериев;»<1000″)

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

В показанном примере, ячейка F7 содержит эту формулу:

=СУММЕСЛИМН(D5:D11;D5:D11; «>500″;D5:D11;»<1000»)

Эта формула суммирует суммы в столбце D, когда они больше 500 и меньше 1000.

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

В этом случае, мы хотим просуммировать значения в столбце D, которые соответствуют двум критериям. Поэтому диапазон суммы — «сумма» (Д4:Д11), а затем два ряда/пары критериев:

сумма;»>500″ // критерии 1
сумма;»<1000″ // критерии 2

С этими критериями, функция СУММЕСЛИМН суммирует все суммы больше 500 и меньше 1000.

Отметим, что оба оператора (>, <) и пороговых значения заключены в двойные кавычки («»).

Если вы хотите включить пороговое значение чисел в сумме (включая суммы, которые равны 500 и 1000), больше или равно (>=) и меньше или равно (<=), вот так:

=СУММЕСЛИМН(сумма;сумма;»>=500″;количество;»<=1000″)

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

=СУММЕСЛИМН(сумма;сумма;»>=»&А1;сумма;»<«&В1)

где A1-это ссылка на нижний порог и B1-это ссылка на верхний порог

Сумма, если за год

=СУММЕСЛИМН(ранг-суммы;Дата;»>=»&Дата(Год);Дата;»<=»&Дата(год;12;31))

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

В показанном примере, формула в Н6 является:

=СУММЕСЛИМН(сумма;Дата;»>=»&Дата(G6;1;1);даты;»<=»&Дата(G6;12;31))

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

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

Диапазон суммы в этом примере — это именованный диапазон, называемый «сумма» (Е3:E2931), и критерии поставляются в виде двух пар, как с помощью именованного диапазона под названием «Дата» (Б3:B2931).

Каждый раз функция ДАТА используется в критериях для построения двух допустимых дат, как с помощью того же года:

1. В первый день 2011 года
2. В последний день 2011 года

Эти даты появляются в виде формулы:

Дата; «>=»&Дата(G6;1;1) // Дата >= к 1/1/2011
Дата; «<=»&Дата(G6;12;31) // Дата <= 12/31/2011

В результате, формула рассчитывает общую сумму всех сумм только в 2011 году.

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

Сумма, если ячейка содержит текст в другой ячейке

=СУММЕСЛИ(диапазон;»*»&А1&»*»;ранг_суммы)

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

В примере показано, ячейка G7 содержит эту формулу:

=СУММЕСЛИ(C5:C11; «*»&F7&»*»;D5:D11)

Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит текст из ячейки F7.

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».

Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символов» и так далее.

Так, например, можно использовать такие критерии, как «*шляпа*» для поиска текста в любом месте ячейки.

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

Обратите внимание, что СУММЕСЛИ нечувствительна к регистру.

Альтернатива с СУММЕСЛИМН

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

=СУММЕСЛИМН(D5:D11;С5:С11;»*кофта*»)

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

Сумма, если ячейки равны

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

В показанном примере, мы подсчитываем все продажи в Западном регионе. Формула в ячейке H4 является:

=СУММЕСЛИ(C5:C14; «Западный»;E5:E14)

Формула в ячейке Н5 является:

=СУММЕСЛИМН(E5:E14;C5:C14;»Западный»)

Обе формулы относятся к названной области диапазонов (С5:C14) и сумм (Е5:Е14).

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

СУММЕСЛИ(диапазон;критерий;ранг_суммы)
СУММЕСЛИМН(ранг_сцммы;диапазон;критерий)

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

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

Сумма, если ячейки не равны

=СУММЕСЛИ(диапазон;»<>значение»;ранг_суммы)

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

В примере показано, ячейка Н5 содержит эту формулу:

=СУММЕСЛИ(C5:C14; «<>Западный»;E5:E14)

Эта формула суммирует суммы в колонке » Е » только тогда, когда регион в столбце C — не «Западный».

Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, включая «не равно», который вводится в качестве <>.

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

Альтернатива с СУММЕСЛИМН

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

=СУММЕСЛИМН(объем; регион;»<>Западный»)

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

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

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

​Смотрите также​ отказа от самых​ интернете, но ни​Значит, будем применять команду​ Заключаем слово в​ версии Excel? Если​Диапазон_суммирования​33​ Диапазон, в котором проверяется​​Excel​​ формула. =СУММЕСЛИ(A2:A7;1;C2:C7)​​ на «ы» («Помидоры»​​2 000 000 ₽​*​ диапазону, а просуммировать​ Для этого воспользуемся​Если среди суммируемых значений​

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

​В этом уроке мы​​ больших сумм, а​​ один не работает​ СУММЕСЛИМН.​ кавычки и ставим​ да, ознакомьтесь с​, содержит значение ИСТИНА​Морковь​Условие1​можно посчитать, только,​Мы рассмотрели этот​ и «Апельсины»).​

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

​ не будем рассматривать,​​ потом «добора» из​​Казанский​Прописываем аргументы.​ вторым аргументом.​ темами на портале​ или ЛОЖЬ.​Ольга​

​.​​ отрицательные или, только,​​ вариант установки формулы​43 000 ₽​3 000 000 ₽​ одному любому символу,​ другого диапазона. Например,​Нажав​​ функция​​ как посчитать сумму​ маленьких.​: Посмотрите внимательно на​диапазон суммирования – ячейки​Диапазон суммирования – это​

​ пользовательских предложений для​​Значения ИСТИНА и ЛОЖЬ​​Формула​Диапазон_условия1​ положительные числа. Об​

​ в ячейку, чтобы​=СУММЕСЛИ(A2:A7;»»;C2:C7)​210 000 ₽​​ а звездочка — любой​​ формула​Enter​СУММ​ в Excel при​Для этого список​ порядок аргументов этой​

​ с зарплатой;​​ заработные платы, потому​​ Excel.​ в диапазоне ячеек,​Описание​и​ этом способе, читайте​

​ понять принцип работы​Объем продаж всех продуктов,​4 000 000 ₽​​ последовательности символов. Если​​=СУММЕСЛИ(B2:B5; «Иван»; C2:C5)​получим результат:​их игнорирует, т.е.​

​ помощи оператора сложения,​ объектов сортируется по​ функции. Он отличается​диапазон условия 1 –​

​ что нам нужно​​Суммировать в программе Excel​​ заданных аргументом​=СУММЕСЛИМН(A2:A9; B2:B9; «=Я*»; C2:C9;​Условие1​ в статье «Сумма​ функции «СУММЕСЛИ». Когда​ категория для которых​280 000 ₽​ требуется найти непосредственно​суммирует только те​

​Если одного условия Вам​ не включает в​ автосуммы и прочих​ сумме обязательств по​

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

​ от порядка аргументов​​ ячейки с указанием​​ узнать сумму зарплат​ умеет, наверное, каждый.​Диапазон_суммирования​ «Артем»)​составляют пару, определяющую,​​ отрицательных чисел в​​ поймем принцип составления​ не указана.​

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

​ убыванию и формула​ функции СУММЕСЛИМН.​ должности сотрудника;​ всех продавцов. Поэтому​ Но с усовершенствованной​

​, оцениваются по-разному, что​Суммирует количество продуктов, названия​

​ к какому диапазону​ Excel».​ формулы с функцией​4 000 ₽​Описание​ звездочку), необходимо поставить​

​ C2:C5, для которых​ Вы всегда можете​Если же текстовые значения​ разберем всего две​ досчитает остальное.​PolzovatelPK​условия 1 – продавец;​ F2:F14.​ версией команды СУММ,​ может приводить к​

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

​ функции:​Решение не самое​: я ошибся в​диапазон условия 2 –​Получилось 92900. Т.е. функция​

​ которая называется СУММЕСЛИ,​​ непредвиденным результатам при​​Я​

​ при поиске. Соответствующие​ удалить дубли в​ написать, сразу, в​Рассмотрим одну из​​=СУММЕСЛИ(A2:A5;»>160000″;B2:B5)​​ «тильда» (​ диапазона B2:B5 равны​СУММЕСЛИМН​ суммирования, то формула​

​СУММ​ эффективное, зато наименее​ 2х буквах…у меня​ ячейки с указанием​ автоматически проработала список​​ существенно расширяются возможности​​ их суммировании.​​и которые были​​ значения найденных в​ таблице, тоже применяем​ ячейке, не вызывая​ популярных​Сумма комиссионных за имущество​~​ «Иван».​, которая позволяет осуществлять​ вернет ошибку:​и​ трудозатратное.​ СУмМЕСЛИ, а надо​ пола сотрудника;​ должностей, выбрала из​

​ данной операции.​

office-guru.ru

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

​Ячейки в аргументе​​ проданы продавцом​ этом диапазоне ячеек​ функцию «СУММЕСЛИ». Смотрите​ окно функции.​функций в​ стоимостью больше 1​).​Если необходимо выполнить суммирование​ условное суммирование в​Функция​СУММЕСЛИ​adrinalinka​ суммеслиМН !!! :-D​условие 2 – женский​ них только продавцов​По названию команды можно​Диапазон_суммирования​Артем​ суммируются в пределах​ статью «Как сложить​

​Получились такие данные.​​Excel «СУММЕСЛИ»​​ 600 000 ₽.​Функция СУММЕСЛИ возвращает неправильные​ ячеек в соответствии​ Excel на основе​СУММ​. Спешу Вас обрадовать,​: Спасибо!! Только обязательно​nikos1981​ (ж).​ и просуммировала их​​ понять, что она​

​, которые содержат значение​. Подстановочный знак (*)​ аргумента​

​ и удалить дубли​​Второй пример.​

• ​. Эта функция выбирает​630 000 ₽​ результаты, если она​ с несколькими условиями,​ нескольких критериев.​достаточно универсальна и​​ их функционала достаточно,​​ нужно, чтобы в​: прикрепи файл, посмотрим.​Итог: все продавцы-женщины в​ зарплаты.​ не просто считает​ ИСТИНА, возвращают 1,​

• ​ в аргументе​Диапазон_суммирования​ в Excel».​Пример функции «СУММЕСЛИ» в​

Синтаксис

​ и складывает данные​

​=СУММЕСЛИ(A2:A5; «>160000»)​​ используется для сопоставления​​ используйте функцию СУММЕСЛИМН.​

• ​Суммирование – это одно​​ позволяет использовать в​ чтобы решать практически​ строке «Проверка» было​PolzovatelPK​ январе получили в​Аналогично можно подсчитать зарплаты​ сумму, но еще​ тогда как ячейки,​Условие1​.​Ещё примеры этой​Excel по дате.​ из таблицы по​Сумма по имуществу стоимостью​

• ​ строк длиннее 255 символов​​СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])​ из основных действий,​ качестве своих аргументов​ все возможные вопросы​ положительное число. Т.е.​: уже не надо,​ сумме 51100 рублей.​ всех менеджеров, продавцов-кассиров​ и подчиняется каким-либо​ которые содержат значение​(​

  ​Условие1​​ функции смотрите в​Теперь посчитаем сумму​ определенным условиям, которые​ больше 1 600 000​ или применяется к​​Аргументы функции​​ которое пользователь выполняет​ не только ссылки​ суммирования в Excel.​

• ​ резерва должно быть​​ сделал. товарищ сверху​Функции СУММЕСЛИ и СУММЕСЛИМН​ и охранников. Когда​ логическим условиям.​ ЛОЖЬ, возвращают 0​​»=Я*»​​    (обязательный аргумент)​​ статье «Функция «СУММЕСЛИ»​​ за определенные даты.​ мы пишем в​ ₽.​​ строке #ЗНАЧ!.​​СУММЕСЛИ​ в Microsoft Excel.​ на ячейки и​

• ​Функция​​ чуточку больше, чтобы​​ навёл на ошибку​ хороши тем, что​ табличка небольшая, кажется,​​Функция СУММЕСЛИ позволяет суммировать​​ (нуль).​​) используется для поиска​​Условие, определяющее, какие ячейки​ в Excel, примеры»​ Можно в формуле​ формуле.​9 000 000 ₽​Аргумент​описаны ниже.​ Функции​ диапазоны, но и​​СУММ​​ полностью покрыть обязательства​

Примечания

• ​adrinalinka​ они автоматически подстраиваются​ что все можно​ ячейки, которые удовлетворяют​Действие​ соответствующих названий продуктов​

• ​ суммируются в аргументе​​ здесь. В этой​​ указать эту дату.​Разберем​=СУММЕСЛИ(A2:A5;300000;B2:B5)​​диапазон_суммирования​​Диапазон​СУММ​ различные математические операторы​вычисляет сумму всех​ и еще осталось.​​: Здравствуйте, очень нужна​​ под изменение условий.​ сосчитать и вручную,​ определенному критерию (заданному​Описание​​ в диапазоне ячеек,​​Диапазон_условия1​

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

Примеры

Пример 1

​ функции Excel:​ является наиболее часто​ минимальная сумма резерва​В столбце есть​ изменить данные в​ со списками, в​ следующие:​Подстановочные знаки, такие как​Диапазон_условия1​ вводится в следующем​ для поиска конкретных​ ячейки, в которой​У нас такая​

Пример 2

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

​ мы забыли учесть​

support.office.com

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

​ конце добавляются еще​ ячейки из диапазона​​ поиска сходных, но​ ​ заданных аргументом​​B4​ «СУММЕСЛИМН». Про эту​ пишем нужную дату.​ на какую сумму​ C2.​ ячейка аргумента​
​ ячейки, содержащие текстовые​​ базовые функции суммирования​ ​позволяет подсчитать условную​
​ обычным оператором суммирования:​ резерва. А Надо​
​ сумму резервного фонда,​
​ одну сотрудницу, которая​ ​ две буквы –​
​ будут выбраны (записывается​ не совпадающих значений.​Диапазон_условия2​,​ функции читайте в​Диалоговое окно функции​ продал продукты первый​
​490 000 ₽​ ​диапазон_суммирования​​ значения, пропускаются. Выбранный​ в Excel, и​ сумму в Excel,​Но мы также можем​ исключить именно такие​
​ надо определить сумму,​ работает продавцом. Мы​ МН (СУММЕСЛИМН), значит,​
​ в кавычках).​Вопросительный знак соответствует любому​(C2:C9). Затем функция​»яблоки»​ статье «Суммирование ячеек​ заполнили так.​ отдел за все​Скопируйте образец данных из​
​, а затем суммируются​ диапазон может содержать​ теперь Вы сможете​ т.е. сумму ячеек,​ воспользоваться функцией​ объекты, которые бы​ на которую надо​ можем добавить еще​ подразумевается функция с​Диапазон суммирования – фактические​ отдельно взятому символу,​ суммирует соответствующие обоим​или​ в Excel по​Нажимаем «ОК». Формула получилась​ дни. В ячейке​ приведенной ниже таблицы​
​ ячейки части диапазона,​ даты в стандартном​ свободно применять эти​ которые удовлетворяют определенному​СУММ​ давали минимальный остаток​ обязательства уменьшить, чтобы​
​ одну строчку через​ несколькими условиями. Она​
​ ячейки, которые необходимо​ звездочка — любой последовательности​
​ условиям значения в​»32″​ условию» тут.​ такая. =СУММЕСЛИ(D2:D7;B10;C2:C7)​ С9 устанавливаем функцию​ и вставьте его​ соответствующей по размерам​ формате Excel (см.​ знания на практике.​ критерию. Функция​и записать формулу​
​ резерва.​
​ резервного фонда полностью​
​ правую кнопку мыши​ ​ применяется в случае,​
​ просуммировать, если они​ символов. Если требуется​ диапазоне ячеек, заданном​.​Какие ещё, есть​Третий пример.​ «СУММЕСЛИ».​ в ячейку A1​ аргументу​ примеры ниже).​ Удачи Вам и​
​СУММЕСЛИ​ следующим образом:​
​Надеюсь, понятно объяснила…​ хватило, и оставался​​ и команду ВСТАВИТЬ.​ когда нужно задать​
​ удовлетворяют критерию.​
​ найти именно вопросительный​ ​ аргументом​
​Диапазон_условия2, Условие2, …​ способы суммирования в​Сумма ячеек​В​ нового листа Excel.​диапазон​Условие​ успехов в изучении​
​может содержать только​Поскольку функция​Я уже себе​ бы самый минимальный​У нас появилась дополнительная​ не один критерий.​Получается, что у функции​ знак или звездочку,​
​Диапазон_суммирования​    (необязательный аргумент)​ Excel, не обязательно​Excel с условием.​Excel функция «СУММЕСЛИ»​ Чтобы отобразить результаты​
​. Пример:​
​   . Обязательный аргумент. Условие​ ​ Excel!​
​ один критерий.​СУММ​ голову сломала, как​ остаток резерва из​ строчка. Сразу обращаем​Аргументов у СУММЕСЛИМН может​ всего 3 аргумента.​ нужно ввести перед​(A2:A9). Результат — 20.​Дополнительные диапазоны и условия​ формулами, смотрите в​Посчитаем сумму продаж​
​находится на закладке​ формул, выделите их​Диапазон​ в форме числа,​Автор: Антон Андронов​
​Например, следующая формула суммируем​ ​поддерживает работу не​​ это сделать с​ всех возможных. Корректировку​ внимание, что диапазон​ быть сколько угодно,​ Но иногда последний​ искомым символом значок​=СУММЕСЛИМН(A2:A9; B2:B9; «<>Бананы»; C2:C9;​
​ для них. Можно​ статье «Суммирование в​ сметаны по всем​ «Формулы» в разделе​ и нажмите клавишу​Диапазон суммирования​ выражения, ссылки на​
​Примечание:​ только положительные числа​ только с отдельными​ помощью формул…​ надо выбрать из​ условий и суммирования​ но минимум –​ может быть исключен,​ тильды (~).​
​ «Артем»)​ ввести до 127 пар​ Excel».​ отделам за все​ «Библиотека функций». Нажимаем​ F2, а затем —​Фактические ячейки​ ячейку, текста или​ Мы стараемся как можно​
​ диапазона A1:A10. Обратите​ ячейками, но и​adrinalinka​ суммы обязательств. Т.е.​ автоматически расширился до​ это 5.​
​ и тогда команда​Например, формула =СУММЕСЛИМН(A2:A9; B2:B9;​Суммирует количество продуктов, которые​ диапазонов и условий.​При написании формулы,​ дни. В ячейке​ на «Математические» и​ клавишу ВВОД. Кроме​A1:A5​ функции, определяющее, какие​ оперативнее обеспечивать вас​ внимание, что условие​

excel-office.ru

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

​ целыми диапазонами, то​: Вариант Поиском решения​ надо так просуммировать​ 15 строки.​Диапазон суммирования. Если в​ будет работать только​ «=Я*»; C2:C9; «Арте?»)​ не являются бананами​Чтобы использовать эти примеры​ мы можем допустить​ В11 будем писать​ выбираем функцию «СУММЕСЛИ».​

​ того, вы можете​B1:B5​ ячейки необходимо суммировать.​

Синтаксис

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

• ​ вышеприведенную формулу можно​adrinalinka​

• ​ обязательства, чтобы это​Копируем данные сотрудника и​

Примеры

​СУММ​ — не самый​ осталась минимальная сумма​ в итоговых ячейках​ на первом месте.​Рассмотрим простейший пример, который​​ и оканчивающимся любой​​ помощью оператора​ правой кнопкой мыши​ что они означают,​Эту функцию можно​​В строке «Диапазон»​​ данными.​​B1:B5​​ «>32», B5, «32»,​

Распространенные неполадки

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

​ январь-месяц. Если нам​

У вас есть вопрос об определенной функции?

​ третьим. Этот момент​ заданных аргументом​

Помогите нам улучшить Excel

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

support.office.com

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

​ удобства также приводим​Во всех примерах, приведенных​ провозиться придется долго.​Всем спасибо огромное!​ чтобы из списка​ новые заработные платы)​Диапазон условия 2 –​

​ нужно просто посчитать​ часто является источником​Диапазон_условия2​Продукт​ с помощью этой​ «Как сделать бланк​

СУММЕСЛИ и ее синтаксис

​ Здесь можно указать​8 000 ₽​ разное число ячеек,​Диапазон_суммирования​ ссылку на оригинал​

1. ​ ранее, мы производили​В следующем примере функция​У меня тоже​ выбрать и просуммировать​
2. ​ и т.п.​ ячейки, которые следует​ общее количество денег,​ проблем при использовании​
3. ​(C2:C9). Затем функция​Продавец​ функции можно найти​ в Excel» здесь.​

​ не только номер​Масло​ пересчет листа может​   . Необязательный аргумент. Ячейки,​ (на английском языке).​ суммирование и проверяли​СУММ​ получилось все-таки решить​

​ те обязательства, от​

Как работает функция СУММЕСЛИ в Excel?

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

​Четвертый пример.​ отдела (например, 1),​4 000 ₽​ занять больше времени,​ значения из которых​Функция​ условие по одному​складывает весь столбец​ эту задачу другим​ которых нужно отказаться,​: Имеем столбец I-итог​ второго критерия.​

​ работникам, мы используем​При копировании и изменении​ условиям значения в​Яблоки​ продавцов, (1) проживающих​Сумма​

​ но и текст,​

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

​Скопируйте образец данных из​ отличаются от ячеек,​используется, если необходимо​ диапазону. А что​ 1048576 значений:​ кому-то пригодится.​

​ обязательств не перекрывала​ фирмы. требуется суммировать​ ячейки, которые функция​ диапазоном все заработные​ нужно следить за​ аргументом​4​ (2) чей доход​Если нужно посчитать​ ячейки с номером​42 000 ₽​

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

​ следующей таблицы и​ указанных в качестве​ просуммировать значения диапазона,​ делать, если необходимо​Следующая формула подсчитывает сумму​adrinalinka​ резервный фонд?​ все заказы определённой​ выделит из второго​ платы.​

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

​ правильным порядком аргументов.​Диапазон_суммирования​Яблоки​ превышает установленный уровень.​

1. ​ сумму по двум​ отдела, код, т.​Фрукты​ вставьте их в​диапазона​ соответствующие указанному условию.​ просуммировать один диапазон,​ всех ячеек, содержащихся​
2. ​:​adrinalinka​ фирмы при том,​ диапазона условия.​
3. ​Но как быть, если​Одинаковое количество строк и​(A2:A9). Результат — 30.​Ольга​
4. ​Это видео — часть учебного​ и более столбцам,​ д. Какие символы​Яблоки​
5. ​ ячейку A1 нового​. Если аргумент​ Предположим, например, что​ а условие проверять​

​ на рабочем листе​adrinalinka​: Да, задача в​ что фирмы в​И так далее. В​ нам нужно быстро​ столбцов для аргументов,​

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

​Проблема​15​ курса Усложненные функции​ то можно в​ можно использовать в​12 000 ₽​

• ​ листа Excel. Чтобы​
• ​диапазон_суммирования​

​ в столбце с​ по-другому?​

​Лист1​

• ​,добрый вечер, макрос запускается​ этом…​
• ​ столбце Q записаны​ зависимости от количества​ посчитать заработные платы​
• ​ задающих диапазоны ячеек​
• ​Описание​Артишоки​ ЕСЛИ.​
• ​ формулу вставить несколько​ формуле, что они​

​Формула​ отобразить результаты формул,​опущен, Excel суммирует​

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

​ числами необходимо просуммировать​На этот случай функция​. Чтобы данная формула​ через CTRL+P, перед​Сорри, что так​ хаотично. пробую формулой​ критериев, число аргументов​ только продавцов? В​Аргумент​Вместо ожидаемого результата отображается​Артем​СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2;​ функций «СУММЕСЛИ». Например,​ означают, смотрите в​Описание​ выделите их и​ ячейки, указанные в​ только значения, превышающие​

​СУММЕСЛИ​ не вызвала циклической​ запуском устанавливаете лимит​ непонятно написала…​ Код =СУММЕСЛИ(I:I;Q:Q;»фирма3″) короче​ может увеличиваться в​

​ дело вступает использование​Диапазон_условия​ 0 (нуль).​3​ условие2]; …)​ в нашу таблицу​ статье «Символы в​Результат​

​ нажмите клавишу F2,​ аргументе​ 5. Для этого​припасла третий необязательный​ ошибки, ее необходимо​ для корректировки. Можете​Genbor​ говоря, задаём фирму(допустим​ арифметической прогрессии с​

exceltable.com

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

​ функции СУММЕСЛИ.​​должен иметь то​Если выполняется поиск текстовых​Артишоки​=СУММЕСЛИМН(A2:A9; B2:B9; «=Я*»; C2:C9;​ из примера добавим​ формулах Excel» тут.​=СУММЕСЛИ(A2:A7;»Фрукты»;C2:C7)​ а затем — клавишу​диапазон​ можно использовать следующую​ аргумент, который отвечает​ использовать на другом​ подшаманить макрос под​: Да нет, написано​ фирма 3) ексель​ шагом 2. Т.е.​Прописываем аргументы.​ же количество строк​ значений, например имени​
​Ольга​ «Артем»)​ столбец В с​

​В строке «Диапазон​​Объем продаж всех продуктов​ ВВОД. При необходимости​(те же ячейки,​ формулу:​ за диапазон, который​

​ рабочем листе Excel​​ себя (к примеру​ все нормально. Просто​ смотрит в столбце​ 5, 7, 9…​

​Диапазоном в данном случае​​ и столбцов, что​

​ человека, убедитесь в​​22​=СУММЕСЛИМН(A2:A9; B2:B9; «<>Бананы»; C2:C9;​ новыми данными. Формулу​

CyberForum.ru

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

​ суммирования» мы указываем​​ категории «Фрукты».​ измените ширину столбцов,​
​ к которым применяется​=СУММЕСЛИ(B2:B25;»>5″)​ необходимо просуммировать. Т.е.​ (отличном от Лист1).​ поставить шаг для​ по логике нужно​ Q все записи​Предположим, нам нужно подсчитать​ будет являться список​ и аргумент​ том, что значения​Бананы​ «Артем»)​ в ячейке напишем​ диапазон столбца с​20 000 ₽​ чтобы видеть все​ условие).​Это видео — часть​ по первому аргументу​Функция​ лимита корректировки) но​ отказываться от последующих​ фирма 3, потом​
​ сумму заработных плат​ всех должностей сотрудников,​Диапазон_суммирования​ аргументов​Артем​
​Имя аргумента​ такую. =СУММЕСЛИ(A2:A7;B10;C2:C7)+СУММЕСЛИ(A2:A7;B10;D2:D7)​

​ данными, которые нужно​​=СУММЕСЛИ(A2:A7;»Овощи»;C2:C7)​
​ данные.​В аргументе​ учебного курса сложение​ функция проверяет условие,​СУММ​ макрос будет думать​ обязательств, если они​ ищет все числовые​

​ за январь всех​​ потому что нам​.​
​Условие1, 2​12​

​Описание​​Этой формулой мы​ посчитать. У нас​Объем продаж всех продуктов​Стоимость имущества​условие​ чисел в Excel.​ а третий подлежит​может принимать до​
​ в данном случае​ начинают превышать резервный​ значения напротив фирма​ продавцов-женщин. У нас​ нужно будет определить​К началу страницы​
​заключены в кавычки.​Бананы​Диапазон_суммирования​ считаем выборочные данные​ – это столбец​
​ категории «Овощи».​Комиссионные​можно использовать подстановочные​

​Советы:​​ суммированию.​ 255 аргументов и​ долго, лучше оставить​ фонд… Это и​ 3 в столбике​ есть два условия.​ сумму заработных плат.​Задать вопрос на форуме​Неверный результат возвращается в​Ольга​    (обязательный аргумент)​
​ из столбцов C​ «Сумма».​120 000 ₽​Данные​ знаки: вопросительный знак​ ​В следующем примере мы​ суммировать сразу несколько​ так как есть.​ смутило.​
​ I, суммирует и​
​ Сотрудник должен быть:​ Поэтому проставляем E2:E14.​ сообщества, посвященном Excel​ том случае, если​

​10​​Диапазон ячеек для суммирования.​

​ и D по​​Заполнили диалоговое окно​=СУММЕСЛИ(B2:B7;»*ы»;C2:C7)​1 000 000 ₽​ (​При необходимости условия можно​
​ просуммируем общую стоимость​ несмежных диапазонов или​Спасибо! Буду пробовать​Как вариант задачку​ выдаёт результат.прилагаю скрин.​

​продавцом;​​Критерий выбора в нашем​У вас есть предложения​ диапазон ячеек, заданный​
​Морковь​
​Диапазон_условия1​ первому отделу.​ так.​Объем продаж всех продуктов,​70 000 ₽​

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

​ названия которых заканчиваются​2 500 000 ₽​

CyberForum.ru

​) и звездочку (​

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

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

Разбор синтаксиса СУММЕСЛИ

Начать стоит с правил ввода составляющих функции СУММЕСЛИ. Из ее частей формируется СУММЕСЛИМН, поэтому детальное описание второй опустим, а вернемся к ней только в завершающем разделе статьи. Стандартная запись функции выглядит как =СУММЕСЛИ(A1:A10;»>1″). 

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

Задать значение для «Диапазон_суммирования» нужно только в том случае, если вы ищете значения больше или меньше конкретного, но при этом добавлять в сумму необходимо ячейки из другого столбца (пока это звучит непонятно, но далее вы увидите такое представление функции на наглядном примере).

Запись СУММЕСЛИ при неравенстве

Еще раз пройдемся по базовому типу использования СУММЕСЛИ. После объявления функции укажите диапазон для проверки в виде A1:A20;. Обязательно поставьте точку с запятой, ведь это нужно для корректной работы функции.

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

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

Использование СУММЕСЛИ с текстом

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

Допустим, у вас есть столбец, в котором несколько строк с названием «Морковь». Нужно сделать так, чтобы функция посчитала сумму всего количества морковок, но не брала во внимание другие овощи и фрукты, записанные в том же столбце. Для этого отлично подойдет рассматриваемая функция, а ее запись в таком случае будет выглядеть так:

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

2. Затем вместо неравенства введите текст для поиска.

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

4. Это то самое значение «Диапазон_суммирования», о котором я говорил при просмотре окна «Аргументы функции».

5. На следующем скриншоте вы видите, что морковок в списке всего две, каждая запись имеет значение 12, а это значит, что в сумму попадают только эти два числа, в результате получается 24.

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

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

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

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

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

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

3. Второй критерий – цена, которая должна превышать 10 за единицу. Соответственно, впишите блок с неравенством A1:A10;»>10″, где A1:A10 – диапазон ячеек, а >10 – критерий.

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

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

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