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 примеры операторов сравнения со ссылками на ячейки
• СУММЕСЛИ примеры формул с подстановочными знаками
• СУММЕСЛИ в Excel примеры с датами
• СУММЕСЛИ в заданном диапазоне дат

СУММЕСЛИ в Excel примеры с логическими операторами (больше, меньше или равно)

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

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

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

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

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

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

А теперь, давайте посмотрим пример формулы «Сумма, если не равно» в действии. Как показано на изображении ниже, формула суммирует количество всех продуктов, кроме «Банана Дамский пальчик»:

=СУММЕСЛИ(A2:A8; «<>Банан Дамский пальчик»; C2:C8)

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с проверкой на неравенство

Примечание. Как и большинство других функций Excel, СУММЕСЛИ нечувствительна к регистру, что означает, что «<> бананы», «<> Бананы» и «<> БАНАНЫ» будут давать точно такой же результат.

СУММЕСЛИ в Excel примеры операторов сравнения со ссылками на ячейки

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

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

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

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ, суммирование исключая значение в ячейке F1

Примечание. Когда вы используете логическое выражение с ссылкой на ячейку, вы должны использовать двойные кавычки («»), чтобы начать текстовую строку и амперсанд (&), чтобы объединить и завершить строку, например «<>» и F1.

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

Формула 1: =СУММЕСЛИ(A2:A8; «=» & F1; C2:C8)

Формула 2: =СУММЕСЛИ(A2:A8; F1; C2:C8)

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

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

Доступны следующие подстановочные знаки:

Звездочка (*) — представляет любое количество символов

Знак вопроса (?) — представляет один символ в определенном месте

Пример 1. Суммирование значений, основанные на частичном совпадении

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

=СУММЕСЛИ(A2:A8; «*бананы*»;C2:C8) — критерий включает текст, заключенный в звездочки (*).

=СУММЕСЛИ(A2:A8; «*»&F1&»*»; C2:C8) — критерий включает ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для конкатенации строки.

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

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

=СУММЕСЛИ(A2:A8; «бананы*»; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A начинается со слова «бананы».

=СУММЕСЛИ(A2:A8; «*бананы»; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A заканчивается словом «бананы».

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

Пример 2. Суммирование по заданному количеству символов

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

=СУММЕСЛИ(A2:A8; «??????»; C2:C8)

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с условием суммирования, если длина текстовой строки в шесть букв

Пример 3. Сумма ячеек, соответствующих текстовым значениям

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

=СУММЕСЛИ(A2:A8; «?*»; C2:C8) – суммирует значения из ячеек C2:C8, если соответствующая ячейка в столбце A содержит не менее 1 символа.

=СУММЕСЛИ(A2:A8; «*»; C2:C8) – учитывает пустые ячейки, содержащие строки нулевой длины, возвращаемые некоторыми другими формулами, например =»».

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

Пример 4. Использование * или ? как обычные символы

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

=СУММЕСЛИ(A2: A8; «~?»; C2: C8)

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

СУММЕСЛИ в Excel примеры с датами

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

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

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

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

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

СУММЕСЛИ в заданном диапазоне дат

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

=СУММЕСЛИ(B2:B9; «>=01.11.2017»; C2:C9) — СУММЕСЛИ(B2:B9; «>=01.12.2017»; C2:C9)

Эта формула суммирует значения в ячейках C2:C9, если дата в столбце B находится между 1 ноября 2017 года и 30 ноября 2017, включительно.

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

Эта формула может показаться немного сложной с первого взгляда, но при более близком рассмотрении это выглядит довольно просто. Первая функция СУММЕСЛИ объединяет все ячейки в C2:C9, где соответствующая ячейка в столбце B больше или равна дате начала (в данном примере 1 ноября). Затем вам просто нужно вычесть значения, которые попадают после даты окончания (30 ноября), с помощью второй функции СУММЕСЛИ.

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

Хитрости »

11 Июнь 2011              346263 просмотров

Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям

Представим себе таблицу, в которой в строках вперемешку указаны названия отделов(или счетов, или еще чего-то).

Суммируем ячейки по критерию
Необходимо вычислить общую сумму по каждому отделу. Многие делают это при помощи фильтра и записи ручками в ячейки.
Хотя сделать это можно легко и просто при помощи всего одной функции — СУММЕСЛИ.
СУММЕСЛИ(SUMIF) – Суммирует ячейки, удовлетворяющие заданному условию (условие можно задать только одно). Эту функцию так же можно применить, если таблица разбита в столбцах на периоды(помесячно, в каждом месяце по три столбца — Доход|Расход|Разница) и необходимо подсчитать общую сумму за все периоды только по Доходу, Расходу и Разнице.

Всего для СУММЕСЛИ предусмотрено три аргумента: Диапазон, Критерий, Диапазон_Суммирования.
=СУММЕСЛИ(A1:A20000;A1;B1:B20000)
=SUMIF(A1:A20000,A1,B1:B20000)

• Диапазон(A1:A20000) — указывается диапазон с критериями. Т.е. столбец, в котором искать значение, указанное аргументом Критерий.
• Критерий(A1)- значение(текстовое или числовое, а так же дата), которое необходимо найти в Диапазоне. Может содержать символы подстановки «*» и «?». Т.е. указав в качестве Критерия «*масса*» будут просуммированы значения, в которых встречается слово «масса». При этом слово «масса» может либо встречаться в любом месте текста, либо в ячейке может быть только одно это слово. А указав «масса*», будут просуммированы все значения, начинающиеся на «масса». «?» — заменяет лишь один символ, т.е. указав «мас?а» вы сможете просуммировать строки и со значением «масса» и со значением «маска» и т.д.
  Если критерий записан в ячейке и надо все же использовать подстановочные символы, то можно сделать ссылку на эту ячейку добавив нужное. Допустим, надо просуммировать значения, содержащие слово «итог». Слово «итог» записано в ячейке A1, в столбце A при этом могут встречаться различные по написанию значения, содержащие слово «итог»: «итоги за июнь», «итоги за июль», «итоги за март». Формула тогда должна выглядеть так:
  =СУММЕСЛИ(A1:A20000;»*»&A1&»*»;B1:B20000)
  «*»&A1&»*» — знак &(амперсанд) объединяет несколько значений в одно. Т.е. в результате получится «*итог*».
  Чтобы лучше понять принцип работы формул лучше использовать инструмент Вычислить формулу: Как просмотреть этапы вычисления формул
  Все текстовые критерии и критерии с логическими и математическими знаками необходимо заключать в двойные кавычки (=СУММЕСЛИ(A1:A20000;»итог»;B1:B20000)). Если критерием является число, использовать кавычки не требуется. Если требуется найти непосредственно вопросительный знак или звездочку, необходимо поставить перед ним знак «тильды» (~).
  Про тильду и её особенности можно узнать в этой статье: Как заменить/удалить/найти звездочку?
• Диапазон_Суммирования(B1:B20000)(необязательный аргумент) — указывается диапазон сумм или числовых значений, которые необходимо просуммировать.

Как это работает: функция ищет в Диапазоне значение, указанное аргументом Критерий, и при нахождении совпадения суммирует данные, указанные аргументом Диапазон_Суммирования. Т.е. если у нас в столбце А название отдела, а в столбце В суммы, то указав в качестве критерия «Отдел развития» результатом функции будет сумма всех значений столбца В, напротив которых в столбце А встречается «Отдел развития». Фактически Диапазон_Суммирования может не совпадать по размеру с аргументом Диапазон и ошибки самой функции это не вызовет. Однако при определении ячеек для суммирования, в качестве начальной ячейки для суммирования будет использована верхняя левая ячейка аргумента Диапазон_Суммирования, а затем суммируются ячейки, соответствующие по размеру и форме аргументу Диапазон.

Некоторые особенности
Последний аргумент функции(Диапазон_Суммирования — B1:B20000) является необязательным. А это значит, что его можно не указывать. Если его не указать, то функция просуммирует значения, указанные аргументом Диапазон. Для чего это нужно. Например, Вам необходимо получить сумму только тех чисел, которые больше нуля. В столбце А суммы. Тогда функция будет иметь такой вид:
=СУММЕСЛИ(A1:A20000;»>0″)

Что следует стоит учитывать: диапазон_суммирования и диапазон должны быть равны по количеству строк. Иначе можно получить неверный результат. Оптимально, если это будет выглядеть как в приведенных мной формулах: диапазон и диапазон_суммирования начинаются с одной строки и имеют одинаковое количество строк: A1:A20000; B1:B20000

Ложка дегтя: — СУММЕСЛИ(а так же СУММЕСЛИМН, СЧЁТЕСЛИ и иже с ними) всегда стремятся преобразовать все значения аргументов к типам. Это значит, что если у нас в ячейке записано число 23 — оно будет воспринято как число. Если тоже число будет записано как текст — «23», то функция преобразует его сначала в число, а потом уже будет работать с ним. Т.е. и 23 и «23» у нас будут считаться одинаковым значением. С одной стороны это хорошо, но иногда такое поведение может сыграть злую шутку. Например, у Вас в ячейках столбца А расположены некие номера счетов, длина которых более 15-ти символов и могут иметь ведущие нули:
00034889913131323455
0034889913131323455
00034889913131323477
как видно, первые два числа почти одинаковые, но у первого числа три ведущих нуля спереди, а второго — два. И это разные счета. А третий счет вообще отличается на последние цифры. Но СУММЕСЛИ после преобразования все три этих значения будет считать как число 34889913131323400 и если записать функцию так: =СУММЕСЛИ($A$1:$A$3;A1;$B$1:$B$3), где в столбце В суммы для каждого счета, то она просуммирует значения для всех трех счетов, считая их одинаковыми. Особо обращаю внимание еще на тот факт, что все числа после 15-го знака будут преобразованы в нули. Эти особенности всегда необходимо учитывать при использовании данных функций, чтобы не попасть в неловкую ситуацию, когда результат будет некорректным.

Суммирование по двум и более критериям
Но что делать, когда критериев для суммирования 2 и больше? Допустим, Вам надо просуммировать только те суммы, которые относятся к одному отделу и только за определенную дату. Счастливые обладатели версий офиса 2007 и выше могут воспользоваться функцией СУММЕСЛИМН:
=СУММЕСЛИМН($C$2:$C$50;$A$2:$A$50;$I$3;$B$2:$B$50;$H8)
$C$2:$C$50 — диапазон_суммирования. Первым аргументов указывается диапазон ячеек, содержащих суммы, которые и будут собираться в одну.
$A$2:$A$50, $B$2:$B$50 — Диапазон_критерия. Указывается диапазон ячеек, в которых необходимо искать совпадение по критерию.
$I$3, $H8 — критерий. Здесь, как и в СУММЕСЛИ, допускается указание символов подстановки * и ? и работают они так же.

Особенность указания аргументов: сначала указывается диапазон критерия(они пронумерованы) затем через точку-с-запятой указывается непосредственно значение(критерий), которое в этом диапазоне необходимо найти — $A$2:$A$50;$I$3. И никак иначе. Не стоит пытаться сначала указать все диапазоны, а потом критерии к ним — функция выдаст либо ошибку, либо просуммирует не то, что надо.

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

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

Т.к. СУММЕСЛИМН появилась только в версиях Excel, начиная с 2007, то как же быть в таких случаях несчастным пользователям более ранних версий? Очень просто: использовать другую функцию — СУММПРОИЗВ. Не буду расписывать аргументы, т.к. их много и все они являются массивами значений. Данная функция перемножает массивы, указанные аргументами. Я постараюсь описать общий принцип использования этой функции для суммирования данных по нескольким условиям.
Для решения задачи суммирования по нескольким критериям функция будет выглядеть так:
=СУММПРОИЗВ(($A$2:$A$50=$I$3)*($B$2:$B$50=H5);$C$2:$C$50)
$A$2:$A$50 — диапазон дат. $I$3 — дата критерия, за которую необходимо просуммировать данные.
$B$2:$B$50 — наименования отделов. H5 — наименование отдела, данные по которому необходимо просуммировать.
$C$2:$C$50 — диапазон с суммами.

Разберем логику, т.к. многим она будет совершенно не ясна просто при взгляде на данную функцию. Хотя бы потому, что в справке подобное её применение не описывается. Для большей читабельности уменьшим размеры диапазонов:
=СУММПРОИЗВ(($A$2:$A$5=$I$3)*($B$2:$B$5=H5);$C$2:$C$5)
Итак, выражение ($A$2:$A$5=$I$3) и ($B$2:$B$5=H5) являются логическими и возвращают массивы логических ЛОЖЬ и ИСТИНА. ИСТИНА, если ячейка диапазона $A$2:$A$5 равна значению ячейки $I$3 и ячейка диапазона $B$2:$B$5 равна значению ячейки H5. Т.е. получается у нас следующее:
=СУММПРОИЗВ({ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}*{ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ};$C$2:$C$50)
Как видно, в первом массиве два совпадения условию, а во втором одно. Далее эти два массива перемножаются(за это отвечает знак умножения(*)). При перемножения происходит неявное преобразование массивов ЛОЖЬ и ИСТИНА в числовые константы 0 и 1 соответственно({0;1;1;0}*{0;0;1;0}). Как известно, при умножении на нуль получаем нуль. И в результате получается один массив:
=СУММПРОИЗВ({0;0;1;0};$C$2:$C$50)
Далее происходит уже перемножение массива {0;0;1;0} на массив чисел в диапазоне $C$2:$C$50:
=СУММПРОИЗВ({0;0;1;0};{10;20;30;40})
И как результат получаем 30. Что нам и требовалось — мы получаем лишь ту сумму, которая соответствует критерию. Если сумм, удовлетворяющих критерию будет больше одной, то они будут просуммированы.

Преимущество СУММПРОИЗВ

• Если у аргументов вместо знака умножения указать знак плюс:
  ($A$2:$A$5=$I$3)+($B$2:$B$5=H5)
  то условия будут сравниваться по принципу ИЛИ: т.е. суммироваться итоговые суммы будут в случае, если хотя бы одно условие выполняется: или $A$2:$A$5 равна значению ячейки $I$3 или ячейка диапазона $B$2:$B$5 равна значению ячейки H5.
  В этом преимущество СУММПРОИЗВ перед СУММЕСЛИМН. СУММЕСЛИМН не может суммировать значения по принципу ИЛИ, только по принципу И(все условия должны выполняться).
• СУММПРОИЗВ не приводит значения к типам, а это значит, что значения вроде 00034889913131323455, 0034889913131323455, 00034889913131323477 будут восприняты как есть, без всякого отсечения ведущих нулей и знаков после 15-го.
• СУММПРОИЗВ может работать с закрытыми книгами. Например, из одной книги суммируется данные по таблице другой книги. После того как закроете книгу-источник, СУММЕСЛИ и СУММЕСЛИМН выдадут ошибку #ЗНАЧ!(#VALUE). А СУММПРОИЗВ продолжит работать как ни в чем не бывало.
• если вдруг потребовалось получить не сумму, а количество ячеек, удовлетворяющих критериям — из СУММПРОИЗВ достаточно удалить диапазон ячеек для суммирования:
  =СУММПРОИЗВ(($A$2:$A$50=$I$3)*($B$2:$B$50=H5))

Недостатки
В СУММПРОИЗВ невозможно использовать символы подстановки * и ?. Точнее использовать можно, но они будут восприняты не как спец.символы, а как непосредственно звездочка и вопр.знак. Я считаю это существенным минусом. И хотя это можно обойти, использую внутри СУММПРОИЗВ иные функции — все же было бы замечательно, если бы функция каким-то образом могла использовать символы подстановки.

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

  Сумма по нескольким критериям (41,5 KiB, 13 649 скачиваний)

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

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

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

Я покажу, как устроен синтаксис этих функций в 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. Полученные знания вы можете использовать в своих целях, выполняя необходимые расчеты и упрощая процесс взаимодействия с электронной таблицей.

Суммирование в 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

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

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

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

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

В примере показано, что ячейка 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, включая «не равно», который вводится в качестве <>.

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

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

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

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

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

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