Суммесли и суммеслимн с учетом регистра в excel и google таблицах

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

​ этой замечательной функции​​ из этого массива​​Функция​ будет такой.​ часть формулы заключаем​ чтобы она складывала​=СУММПРОИЗВ(—ЕПУСТО(D2:D23)) подсчет пустых ячеек​ – ячейка​, содержащий значения 4,​ A5*B5+ A6*B6), т.е.​​ 1*5= 123​ следующая формула​ написано «и т.д.»,​ не учитывать оборот​ закрыть, то появляется​ то умножать можно​ эти формулы сочетанием​​ — применять ее​

​ или ссылки. Пустые​​ПРОИЗВЕД​

​=СУММПРОИЗВ((B2:B8=C12)*(C2:C8=C11)*(D2:D8=C13);E2:E8)​​ в круглые скобки​​ выборочные данные.​

​ 8, 6, 1.​​ эта запись эквивалента​Таким образом, можно найти​подсчитает то что​

​ т.е. таблица растет,​​ этого магазина в​ ошибка #ЗНАЧ! Наша​ не на 1,​Ctrl+Shift+Enter​ так, как описано​ ячейки, логические значения​перемножает все числа,​​Третий способ​ и ставим знак​Не числовые данные​=СУММПРОИЗВ(НАИБОЛЬШИЙ(A:A;<1;2;3>)) сумма 3-х наибольших​​должна содержать число​

​Если мы запишем формулу​​ формула =СУММПРОИЗВ(A3:A6;B3:B6);​ сумму произведений 3-х,​ нужно:​ увеличивается количество систем,​ сумме знаменателя.​ же функция​ а на диапазон​вместо обычного​ в справке -​ и текст в​ переданные как аргументы,​

​.​​ «*» (умножить).​ (текст, напирмер) функция​ значений;​​ 2.​

СУММЕСЛИ (функция СУММЕСЛИ)

Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазон, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;»> 5″)

Это видео — часть учебного курса Сложение чисел в Excel.

При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; «Иван»; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны «Иван».

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

СУММЕСЛИ(диапазон; условие; )

Аргументы функции СУММЕСЛИ описаны ниже.

Диапазон — обязательный аргумент. Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel (см. примеры ниже).

Условие .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Например, условие может быть представлено в таком виде: 32, «>32», B5, «32», «яблоки» или СЕГОДНЯ().

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

Диапазон_суммирования .Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие).

В аргументе условие можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует одному любому символу, а звездочка — любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак «тильда» (

Условная функция ЕСЛИ()

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

Простое условие

Что же делает функция ЕСЛИ()? Посмотрите на схему. Здесь приведен простой пример работы функции при определении знака числа а.

Блок-схема “Простое условие”. Определение отрицательных и неотрицательных чисел

Условие а>=0 определяет два возможных варианта: неотрицательное число (ноль или положительное) и отрицательное. Ниже схемы приведена запись формулы в Excel. После условия через точку с запятой перечисляются варианты действий. В случае истинности условия, в ячейке отобразится текст “неотрицательное”, иначе – “отрицательное”. То есть запись, соответствующая ветви схемы «Да», а следом – «Нет».

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

Блок-схема “Простое условие”. Расчет данных

На схеме видно, что при выполнении условия число увеличивается на десять, и в формуле Excel записывается расчетное выражение А1+10 (выделено зеленым цветом). В противном случае число не меняется, и здесь расчетное выражение состоит только из обозначения самого числа А1 (выделено красным цветом).

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

Решение:

Решение данной задачи видно на рисунке ниже. Но внесем все-таки ясность в эту иллюстрацию. Основные исходные данные для решения этой задачи находятся в столбцах А и В. В ячейке А5 указано пограничное значение дохода при котором изменяется ставка налогообложения. Соответствующие ставки указаны в ячейках В5 и В6. Доход фирм указан в диапазоне ячеек В9:В14. Формула расчета налога записывается в ячейку С9: =ЕСЛИ(B9>A$5;B9*B$6;B9*B$5). Эту формулу нужно скопировать в нижние ячейки (выделено желтым цветом).

В расчетной формуле адреса ячеек записаны в виде A$5, B$6, B$5. Знак доллара делает фиксированной часть адреса, перед которой он установлен, при копировании формулы. Здесь установлен запрет на изменение номера строки в адресе ячейки.

Логическая операция И()

Например: Рассмотрим электронную таблицу “Ведомость сдачи вступительных экзаменов”. Для зачисления абитуриента в ВУЗ, ему необходимо преодолеть проходной балл, и по математике отметка должна быть выше 70 баллов. Посмотрите внимательно на рисунок ниже.

В этом примере функция ЕСЛИ() использует составное условие, связанное логической операцией И()

Обратите внимание: абитуриент Петров не зачислен, хотя сумма его баллов равна проходному

Почему так произошло? Посмотрим внимательно на условие в нашей формуле =ЕСЛИ(И(E6>=D2;B6>70);”зачислен”;”не зачислен”). Логическая операция И() требует выполнения всех условий, но у нас выполняется только одно. Второе условие B6>70 не выполнено, поэтому составное условие принимает значение «ложь». И на экран выводится сообщение «не зачислен» (вспоминаем схему – ветвь «нет»).

1. Рассчитайте общую стоимость продаж. Итого = Стоимость* Количество.

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

3. Определите сумму продажи с учетом скидки. Сумма продажи с учетом скидки = Итого* (1- Скидка%).

Решение:

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

• В ячейке Е7: =B7*C7
• В ячейке F7: =ЕСЛИ(И(D7>=D$4;D7

Абитуриент Сидоров зачислен, хотя не набрал проходной балл. Вот формула =ЕСЛИ(ИЛИ(B7>60;E7>D2;);”зачислен”;”не зачислен”). Здесь использована операция ИЛИ(), поэтому достаточно выполнение хотя бы одного условия. Что и произошло, первое условие B7>60 истинно. Оно привело к выводу сообщения о зачислении абитуриента.

1. Рассчитайте общую стоимость продаж. Итого = Стоимость* Количество.

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

3. Определите сумму продажи с учетом скидки. Сумма продажи с учетом скидки = Итого* (1- Скидка%)

Решение:

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

• В ячейке Е7: =B7*C7
• В ячейке F7: =ЕСЛИ(ИЛИ(D7=D$4;D7=E$4;D7=F$4);B$4;0)
• В ячейке G7: =E7*(1-F7)

и скопировать по соответствующим столбцам до 15 строки включительно.

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

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

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

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

=СУММ()

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

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

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

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

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

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

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

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

Синтаксис

Возможно использование неограниченного количества аргументов, но минимально необходимо указать 5 штук.

1. Диапазон суммирования. Здесь это главный аргумент, который нужно обязательно указывать. Значение то же самое – указание конкретных ячеек, которые нужно просуммировать.
2. Диапазон первого условия. Аргумент, эквивалентный диапазону поиска в функции СУММЕСЛИ. Только в этом случае указывает диапазон первого критерия.
3. Первое условие.
4. Диапазон второго условия. Аргументы, аналогичный диапазону первого условия.
5. Второе условие.

Дальше логика такая же. Указывается диапазон поиска, а потом непосредственно критерий. Таким образом, по мере увеличения количества критерии, количество аргументов увеличивается в арифметической прогрессии, где шаг равен двум: 5,7,9,11 и так далее.

Пример применения

Допустим, нам нужно посчитать сумма зарплаты за месяц для всех женщин-продавцов, которые соответствуют двум критериям:

1. Они являются женщинами.
2. Они являются продавцами.

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

В нашем случае аргументы будут следующими:

1. В качестве диапазона суммирования оставляем тот же диапазон, что и в прошлом примере (поскольку там содержатся зарплаты).
2. Диапазон условия 1 – профессия работника. 
3. Условие 1 – продавец.
4. Диапазон условия 2 – пол работника.
5. Условие 2 – женский

Вот так просто оно работает на практике. Общая сумма денег, которая была получена составила 51100 рублей.

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

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

У нас появилась дополнительная строчка

Сразу обращаем внимание, что диапазон условий и суммирования автоматически расширился до 15 строки

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

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

Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в Excel

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

СУММПРОИЗВ – синтаксис формулы

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

Синтаксис функции выглядит следующим образом: =СУММПРОИЗВ(список1; список2 …)

То есть, если у вас есть массив данных в одной колонке и – в другой, и вы воспользовались функцией СУММПРОИЗВ, вы получите результат 120 (потому что 2*5+3*10+4*20=120).

СУММПРОИЗВ и массивы

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

Вопрос довольно просто решаем с одним условием, но что если количество условий возрастает и нам необходимо узнать сумму продаж конкретного агента в конкретном регионе. У нас есть три пути:

На самом деле у нас есть еще один, скрытый путь, воспользоваться СУММПРОИЗВ.

Использование СУММПРОИЗВ в формулах массива

Если предположить, что данные находятся в диапазоне A2:C21, с именами агентов в колонке A, регионами – в колонке B и продажами – в колонке C, то формула СУММПРОИЗВ будет выглядеть следующим образом:

=СУММПРОИЗВ(- -(A2:A21=»Агент Смит»);- -(B2:B21=»Запад»);C2:C21)

Давайте разберемся, как работает формула:

• Часть формулы (- -(A2:A21=»Агент Смит» ищет Агента Смита в диапазоне A2:A21 и возвращает массив с единицами и нулями (единица, если ячейка содержит Агента Смита, и ноль – если нет).
• Часть — -(B2:B21=»Запад») делает тоже самое, только возвращает единицу, если ячейка содержит Запад.
• C2:C21 – просто возвращает массив с продажами

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

Послесловие

Функция СУММПРОИЗВ сможет гораздо больше, когда вы поймете, как она работает. Данная статья только приоткрывает завесу тайны к данной формуле.

Почему функция СУММЕСЛИ может не работать?

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

1. Диапазоны должны указываться в виде ссылок на диапазон, а не массива. Эта ошибка бывает довольно редко, но это возможно, поскольку многие новички не полностью понимают разницу между диапазоном и массивом.
2. Если суммируются значения из других листов или книг, которые закрыты на момент использования формулы.
3. Диапазон данных и поиска отличаются по размеру.

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

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

​ значку​ ​«Редактирование»​ ​ действует данный способ​
​ ученикам на первых​

excel-office.ru>

​Параметры вычислений​

• Как в xl посчитать сумму в столбце в excel
• Почему эксель неправильно считает сумму
• Посчитать в эксель количество значений в
• Как в эксель посчитать количество ячеек
• Сумма по условию эксель
• Сумма прописью формула эксель
• Как посчитать проценты в эксель по формуле
• Как в таблице excel посчитать сумму столбца автоматически
• В эксель к сумме прибавить проценты в
• Почему эксель не считает сумму
• Эксель посчитать количество ячеек с определенным значением
• Как в эксель посчитать непустые ячейки

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

Функция СУММЕСЛИ популярна, поскольку почти в каждой таблице нужно посчитать сумму чисел в ячейках, игнорируя значения, не попадающие под основное условие. Благодаря этой формуле подсчет не становится чем-то сложным и длительным. Стандартное оформление функции выглядит как =СУММЕСЛИ(Диапазон;Критерий;Диапазон_суммирования), а &#171;Диапазон суммирования&#187; указывается только при условии, когда есть фактические ячейки, сложение которых выполняется при любых обстоятельствах. Если данные в &#171;Диапазоне суммирования&#187; отсутствуют, проверяться будут все ячейки, входящие в &#171;Диапазон&#187;.

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

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

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

1. Определитесь с диапазоном ячеек, попадающих под рассмотрение формулой, у нас это будет прибыль за месяц.

Выше был разобран только один из примеров со случайно взятыми условиями. Ничего не помешает подставить другие значения, расширить или сузить диапазон &#8212; формула все равно нормально посчитает значение, если правила синтаксиса были соблюдены.

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

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

1. В этот раз помимо диапазона суммируемых ячеек определите и те, где присутствуют надписи, попадающие в условие.

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12345 инструкций. Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Функция СУММ в Excel

В программе Excel используются массивы, или данные, которые объединены в группы. Ими являются таблицы с различными значениями. Чтобы узнать сумму целого массива или нескольких больших массивов, используется функция «СУММ

»:

1. Выделите ту ячейку, в которую планируется вставить итоговое значение сложения. Затем кликните по кнопке «Вставить функцию », которая расположена рядом со строкой для формул.
2. Откроется окно мастера функций. Выделите пункт «СУММ », затем кликните по кнопке «ОК ». Чтобы быстро отыскать нужную функцию, можно воспользоваться специальной поисковой строкой.
3. В появившемся окне определите диапазон ячеек, которые нужно суммировать. Сделать это можно вручную, вписывая их значение. Также можно выделить с помощью левой кнопкой мышки нужные данные прямо из таблицы в программе. В строке аргументов функции отобразится диапазон ячеек, после этого можно подтвердить действие с помощью кнопки «ОК ».

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

» в строке «Число 2 » его определить, затем нажать на «ОК ». В выбранной ячейке к ранее полученному результату прибавятся новые данные.

Программа Excel позволяет прописать функцию самостоятельно без использования дополнительных диалоговых окон. Чтобы это сделать, выберите нужную пустую ячейку и в строке формул вручную пропишите функцию с диапазоном. Например, =СУММ(B5:B7), чтобы посчитать итог продаж за четверг, пятницу и субботу.

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

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

СУММ с помощью горячих клавиш

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

1. Выберите пустую ячейку, в которую будет вписана сумма.
2. Нажмите и удерживайте клавишу Alt, затем один раз нажмите на клавишу «равно» =.
3. Отпустите Alt. В выделенную ячейку будет вставлена функция СУММ.
4. Нажмите Enter, чтобы Excel подсчитал выбранный диапазон.

Данное сочетание клавиш работает на Windows. Для операционной системы MacOS функция активируется одновременным нажатием клавиш Shift + Cmd + T.

СУММЕСЛИ или СУММЕСЛИМН в Excel — что лучше использовать?

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

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

Но обратите внимание: 

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

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

Почему СУММЕСЛИМН удобнее при суммировании по одному критерию?

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

Поэтому совет практика: использовать только функцию СУММЕСЛИМН.

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

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

   Сообщество Excel Analytics | обучение Excel

Вам может быть интересно:

• Аргумент интервальный просмотр в функции ВПР
• ВПР и СЧЁТЕСЛИ по части текста
• Как написать римские цифры в Excel
• Как посчитать количество рабочих дней между двумя датами в Excel
• Как написать формулу в Excel и чем отличаются формула и функция

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

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

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

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

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

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

Формула суммы с учетом регистра в Excel

Чтобы просуммировать ячейки с одним условием с учетом регистра букв, вы можете использовать функции СУММПРОИЗВ и ТОЧНОЕ вместе:

СУММПРОИЗВ(—(ТОЧНО(критерии, диапазон)) сумма_диапазон)

С идентификатором цели в E1(критерии), список элементов в формате A2:A10 (диапазон) и номера продаж в B2:B10 (сумма_диапазон), формулы принимают следующий вид:

=СУММПРОИЗВ(—(ТОЧНО(E1, A2:A10)), B2:B10)

При необходимости вы можете «жестко закодировать» критерии прямо в формуле:

=СУММПРОИЗВ(—(ТОЧНО(«A-01», A2:A10)), B2:B10)

Как работает эта формула:

В основе формулы функция EXACT сравнивает целевой элемент (E1) с каждым элементом в списке и возвращает TRUE, если сравниваемые значения в точности совпадают, включая текстовый регистр, в противном случае FALSE:

{ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}

Двойной унарный оператор (—) преобразует ИСТИНА и ЛОЖЬ в 1 и 0 соответственно:

{1;0;0;0;1;0;1;0;0}

Функция СУММПРОИЗВ умножает элементы вышеуказанного массива на соответствующие элементы в ячейках B2:B10:

СУММПРОИЗВ({1;0;0;0;1;0;1;0;0}, {250;155;130;255;160;280;170;285;110})

И поскольку умножение на 0 дает ноль, выживают только те элементы, для которых EXACT возвращает TRUE:

СУММПРОИЗВ({250;0;0;0;160;0;170;0;0})

Наконец, СУММПРОИЗВ складывает произведения и выводит сумму.

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

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

В таковых вариантах при указании в аспекты данного условия его заключают в двойные кавычки.

На рисунке показано, как посчитать сумму всех заказов стоимостью больше 10000 рулей.

Аналогично употребляется функция при остальных критериях, изменяются лишь знаки в области критериев:

• < – меньше;
• = – равно (можно не указывать);
• < > – не равно;
• >= – больше либо равно;
• <= – меньше либо равно.

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