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

Excel Sumifs Function Examples

The spreadsheet below shows the quarterly sales figures for 3 sales representatives.

The Sumifs function can be used to find total sales figures for any combination of quarter, area and sales rep.

This is shown in the examples below.

Example 1

To find the sum of sales in the North area during quarter 1:

=SUMIFS( D2:D13, A2:A13, 1, B2:B13, «North» )

which gives the result $348,000.

The value in column A is equal to 1

The entry in column B is equal to «North»

and calculates the sum of the corresponding values from column D.

I.e. this formula finds the sum of the values $223,000 and $125,000 (from cells D2 and D3).

Example 2

Again, using the data spreadsheet above, we can also use the Sumifs function to find the total sales for «Jeff», during quarters 3 and 4:

=SUMIFS( D2:D13, A2:A13, «>2», C2:C13, «Jeff» )

This formula returns the result $571,000.

In this example, the Excel Sumifs function identifies rows where:

The value in column A is greater than 2

The entry in column C is equal to «Jeff»

and calculates the sum of the corresponding values in column D.

I.e. this formula finds the sum of the values $310,000 and $261,000 (from cells D8 and D11).

For further examples of the Excel Sumifs function, see the Microsoft Office website.

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

• ​=СУММЕСЛИ(B2:B5; «Иван»; C2:C5)​
• ​ Допустим, «Кемерово». Условие​
• ​ математические операторы. Например,​
• ​ того, что нами​
• ​Рассмотрим два возможных варианта:​
• ​ набежавших процентов).​

​ такая формула.​ функцию СУММЕСЛИМН по​СРЗНАЧЕСЛИ​ да, ознакомьтесь с​ 0 (нуль).​.​ итог будет равен​ критерий – равенство​

​ выбора ячеек, включенных​490 000 ₽​ строке #ЗНАЧ!.​​суммирует только те​​ 1 – ссылка​

СЧЕТЕСЛИ

​Ежедневно на сумму просроченной​Для этого будет использоваться​=СУММЕСЛИМН(E2:E8;B2:B8;»Васечкин»;E2:E8;»>500″)​ менеджерам и покупателям.​​(AVERAGEIF) и​​ темами на портале​

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

​ дебиторской задолженности начислялся​ оператор «СУММЕСЛИ» в​​Здесь последний критерий​​Пример 3.​

​ значений, например имени​Продукт​Использование функции СУММЕСЛИ в​И в этом случае​​ кликнуть на нужную,​​ приведенной ниже таблицы​диапазон_суммирования​ C2:C5, для которых​ названием города (С3).​ условия 2; условие​ отношение исключительно к​ 0,1 процент в​

​Нам нужно посчитать​(AVERAGEIFS), чтобы рассчитать​ Excel.​ человека, убедитесь в​​Продавец​​ Excel облегчает выполнение​ мы должны указать​ и ее расположение​ и вставьте его​может не совпадать​ соответствующие значения из​Для учета вида услуг​ 2; … необязательные​ последнему столбцу.​

​ «>500».​ сумму продаж всех​ среднее значение ячеек​СЧЁТ​ том, что значения​​5​​ многих задач. Ее​ колонку слагаемых в​ будет указано в​

​ диапазона B2:B5 равны​​ задаем второй диапазон​ аргументы для назначения​​После того как заполнены​​Исходим из объема рефинансирования​​ с того, что​​Пример 6.​ фруктов, кроме яблок.​ на основе одного​СЧЁТЕСЛИ​

​ аргументов​Яблоки​​ синтаксис прост и​​ поле «Диапазон_суммирования» окна​

​ формуле.​

office-guru.ru>

Как исправить #VALUE! сообщение об ошибке в функции СУММЕСЛИ и СУММЕСЛИМН

​?​ только значения, превышающие​ или звездочка (*),​ в аргументе​и​maslow​

Проблема: формула ссылается на ячейки в закрытой книге

​ — подумаем.​Способ 3: Формула​ листе.ПРИМЕЧАНИЯ: Количество критериев​ аргументам диапазон_условия1; условие1.​ его.​ способы суммирования в​

​ даты. В ячейку​​ и складывает данные​ по улучшению следующей​ это приводит к​=СУММЕСЛИ(A2:A5;»>160000″;B2:B5)​) и звездочку (​ 5. Для этого​ в аргументах​Условие1​Условие1​: Очень интересное решение,​

​vkluchatel​​ массива​ суммирования ограничено оперативной​ Всего может быть​​

​ Excel, не обязательно​ E10 устанавливаем функцию​ из таблицы по​ версии Excel? Если​ ошибке #ЗНАЧ!.​Сумма комиссионных за имущество​*​ можно использовать следующую​Условие1, 2​

Проблема: длина строки условия превышает 255 символов

​(​составляют пару, определяющую,​ но есть одно​: Ок, пример упрощен.​Code =СУММ(ЕСЛИ((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2);F2:F16))​

​ памятью.​​ задано до 127​Пример 2. В таблице​ формулами, смотрите в​ «СУММЕСЛИ».​ определенным условиям, которые​ да, ознакомьтесь с​Примечание.​ стоимостью больше 1​

​). Вопросительный знак соответствует​ формулу:​

​можно использовать для​»=Я*»​ к какому диапазону​ «но»- что если​​Vlad999​​В английской версии:​​ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая​​ условий отбора данных​ указаны данные об​ статье «Суммирование в​

​В ячейке B10​ мы пишем в​ темами на портале​Это известная проблема​ 600 000 ₽.​​ одному любому символу,​​=СУММЕСЛИ(B2:B25;»>5″)​ поиска сходных, но​) используется для поиска​​ применяется определенное условие​ данные в объединённой​​: вариант​​Code =SUM(IF((B2:B16=I2)*(D2:D16=J2)*(A2:A16=K2),F2:F16))​​ версия Excel​​ для суммирования.​​ отгрузках трех товаров​​ Excel».​​ пишем нужную дату.​ формуле.​

​Это видео — часть​​ не совпадающих значений.​ соответствующих названий продуктов​​ при поиске. Соответствующие​​ из 3 ячеек​Код =СУММПРОИЗВ(((($A$1:$H$1=$K$1)*$A$2:$H$900)=—K3)*$B$2:$I$900) помоему​

​КАК ЭТО РАБОТАЕТ:​​Способ 2:​Примечания:​ в различные страны​

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

​Разберем​ Excel.​ функций Excel, в​=СУММЕСЛИ(A2:A5; «>160000»)​ последовательности символов. Если​ учебного курса сложение​Вопросительный знак соответствует любому​

support.office.com>

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

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

Динамический диапазон суммирования по условию

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

Таблица выглядит следующим образом.

1

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

2

Давайте более подробно опишем аргументы:

1. C3:C14 – это наш диапазон суммирования. В нашем случае он совпадает с диапазоном условия. Из него будут отбираться баллы, используемые для расчета суммы, но лишь те, которые подпадают под наши критерии.
2. «>5» – наше первое условие.
3. B3:B14 – второй диапазон суммирования, который обрабатывается на предмет соответствия второму критерию. Видим, что здесь нет совпадения с диапазоном суммирования. Из этого делаем вывод, что диапазон суммирования и диапазон условия может быть как идентичным, так и нет. 
4. «A*» – второй диапазон, который задает отбор оценок лишь тех студентов, чья фамилия начинается на А. Звездочка в нашем случае означает любое количество знаков. 

После расчетов мы получаем следующую таблицу.

3

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

SUMIFS and Wildcard

If we are lucky, there is a uniform pattern in the labels that we can use with wildcards. For example, in the data above, all of the sales accounts happen to start with “Sales-” and that makes us really lucky. We can simply use a wildcard to tell the SUMIFS function to add up any rows where the account begins with Sales- as shown in the formula below:

=SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-*")

Here it is in the report:

Note: Alternatively, we could reference the report label in the cell, and update the third argument to B6&”*”

A similar function could populate the COS report value, as follows:

=SUMIFS($C$18:$C$28,$B$18:$B$28,"COS-*")

And then we could populate the SG&A report value by adding up all of the accounts that do not have a dash, as follows:

=SUMIFS($C$18:$C$28,$B$18:$B$28,"<>*-*")

However, in practice, we are not often lucky enough to have such a pattern. So, let’s explore other options.

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

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

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

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

Создайте открывающую и закрывающую скобку, где введите диапазон выбранных ячеек, например C2:C25. После этого обязательно поставьте знак ;, который означает конец аргумента.

Откройте кавычки и в них укажите условие, что в нашем случае будет >300000.

Как только произойдет нажатие по клавише Enter, функция активируется. На скриншоте ниже видно, что условию >300000 соответствуют лишь две ячейки, следовательно, формула суммирует их числа и отображает в отдельном блоке.

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

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

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

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

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

Следом идет второй аргумент — совпадение по названию в ячейке. Запись этого параметра уже рассматривалась выше и еще раз ее можно увидеть на скриншоте.

Третье условие — соответствие заданному неравенству, с чем мы ознакомились еще в начале статьи. Опять в таком же формате укажите диапазон значений и само неравенство в кавычках. Не забудьте поставить закрывающую скобку, завершив тем самым написание функции.

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

Подробнее: 10 популярных математических функций Microsoft Excel

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

Опишите, что у вас не получилось.

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

Несколько критериев для СУММЕСЛИ()

​ один» повторяет действие​​ выполнялось — получим​Code =DSUM(A1:G16,F1,I1:K2)​ Ашан звездочки. Это​ искомым символом значок​ помощью оператора​Параметры вставки​ число всех розничных​ оператор конкатенации. В​

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

​является первым аргументом,​​ позволяет.​

​ они будут автоматически​​ ячейки с числами,​ будет применен второй​ функции СУММПРОИЗВ в​

​ 0 (1*1*0 =​​КАК ЭТО РАБОТАЕТ:​ значит, что нужно​

​ тильды (~).​​<>​выберите команду​ продавцов, (1) проживающих​ примере ниже формула​

​СУММ​​ (для исключения лишних​ в одной строке​ а в СУММЕСЛИ()​Makes​

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

​ критерий;​​ нашем примере, но​ 0 ; 1*0*1​Из указанной нами​

​ искать все слова​​Например, формула =СУММЕСЛИМН(A2:A9; B2:B9;​​в аргументе​​Использовать форматы конечных ячеек​ в одном регионе,​ вернет сумму значений,​​складывает весь столбец​​ месяцев).​ соответствует Условию И​​ – третьим. При​: Благодарю​​ значениям 1 и​ вычислено суммарное значение​»А*» – второе условие​ не является формулой​ = 0 ;​ базы данных A1:G16​ «Ашан», не зависимо​ «=Я*»; C2:C9; «Арте?»)​

​Условие1​​.​ (2) чей доход​ которые больше значения​ А, а это​

​Альтернативным вариантом для всех​​ (см. диапазон​

planetaexcel.ru>

​ копировании и редактировании​

• Счетесли в excel по двум критериям
• Суммесли в excel формула
• Как в excel работает суммесли
• Excel суммесли на английском
• Функция суммесли в excel примеры
• Функция суммесли в excel
• Как в excel отсортировать данные по нескольким критериям
• Функция суммесли в excel примеры с несколькими условиями
• Критерий фишера в excel
• Excel впр по нескольким критериям
• Excel суммесли формула
• Сумма если в excel примеры 2 критерия

Примеры суммеслимн

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

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

1. Примеры с датами продаж:

Задача: найти количество продуктов проданных 22.02.2019г.

В свободной ячейке пишете следующее: =СУММЕСЛИМН(С2:С8;B2:B8;B2)

1. Примеры с двумя условиями:

Задача: определить сумму яблок, проданных 22.02.2019г.:

Функция будет выглядеть следующим образом: =СУММЕСЛИМН(C2:C8;B2:B8;B2;A2:A8;»Яблоко»)

где B2:B8;B2 – первое условие, а A2:A8;»Яблоко» – второе. Диапазон суммирования по столбцу Количество.

1. Примеры с текстом и звездочкой:

Задача: найти количество яблок, проданных Артемом.

Содержимое пустой ячейки: =СУММЕСЛИМН(C2:C8;A2:A8;»Яблоко»;D2:D8;»А*»)

Использование подстановочного знака звездочка помогает искать продавца, имя которого начинается на букву А. При таком условии считается число яблок, проданных Артемом и Ангелиной, поскольку их имена начинаются на букву А. Чтобы найти именно продажи Артема, необходимо изменить аргумент на «Ар*».

1. Также в числовых столбцах можно использовать операторы сравнения. Если в условие больше меньше или равно, то можно обозначить границы поиска по цифрам.

Задача: Найти дату продажи Ангелиной продуктов больше 10 единиц.

Функция имеет следующий вид: =СУММЕСЛИМН(B2:B8;D2:D8;»Ангелина»;C2:C8;»>10″)

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

Формула суммеслимн по частичному совпадению условия (Формулы/Formulas)

​ принимающий ссылку на​​В результате формула автоматически​​ использования функции «СУММЕСЛИМН»,​ С2:С8.​ выбирает определенные данные​(SUMIF). В данном​ в таблице данные​​ функциях СУММЕСЛИМН() и​AleksSid​​ «текст заканчивается на»,​​ МП 50 мм»;I1:I50;»Маты​ хочу применить 2​ нет​: Никто ничего не​ документы работают с​​ суммирования и условиях​ ячейки, в которых​ определила динамически изменяемый​ смотрите в статье​В строке «Условие3»​ для сложения по​ случае для проверки​ в рамках 1​​ БДСУММ()). Заголовки этой​ СУММЕСЛИ(). В СУММЕСЛИМН()​: Так? Код =СУММПРОИЗВ((ЦЕЛОЕ(Лист2!A2:A6)=A2)*(Лист2!C2:C6=C1)*Лист2!B2:B6)​​ чтобы было для​ МП 50 мм»)​​ условия в формуле​

​jakim​​ чертил, сводная строится​

​ этой функцией довольно​​ должно быть одинаковым,​ содержатся данные для​

​ диапазон в соответствии​​ «Примеры функции «СУММЕСЛИМН»​​ пишем – «яблоки».​​ нескольким условиям. Можно​ условия и суммирования​ года).​ таблицы должны в​ аргумент​tru-lya-lya​ «текст содержит», нужно​

​ (При выборе «Орск»​​: Как вариант.​ сама​ в большом обьеме​

​ иначе функция СУММЕСЛИМН​​ проверки на соответствие​​ с условиями отбора​​ в Excel».​Нажимаем «ОК». Получился такой​ указать до 127​ используется один столбец,​

excelworld.ru>

SUMIFS+SUMIFS

Another option is to simply string together a series of SUMIFS functions, each one designed to aggregate a specific label. For example, we could use SUMIFS to add up all rows where the account is equal to Sales-Labor, and then add to its result a SUMIFS function designed to add up all rows where account is equal to Sales-Hardware, and so on. This idea can be illustrated as follows:

=SUMIFS()+SUMIFS()+SUMIFS()

The corresponding formula to populate the sales report value is shown below.

=SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-Labor")+
SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-Hardware")+
SUMIFS($C$18:$C$28,$B$18:$B$28,"Sales-Software")

When there are only a couple of criteria values, this approach may get the job done. When there are several criteria values, we can simplify the formula and remove the redundancy by using an array criteria value argument. Let’s examine this approach now.

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

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

1. Отметьте ячейку.

2. Нажмите на «fx» или «Вставить функцию».

3. Появится окно, где из перечня выберите функцию «СУММЕСЛИМН».

4. Кликните на «ОК» или два раза нажмите на «СУММЕСЛИМН».

5. Проявится меню «Аргументы функции».

6. Заполните диапазоны условий и сами условия.

7. Нажмите «ОК».

8. Предположим, нам нужно узнать, на какую сумму Васечкин продал яблок. У нас есть всего 2 условия – продавец должен быть Васечкин, а товар – яблоки. В нашем случае, аргументы функции будут выглядеть следующим образом.

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

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

SUM(SUMIFS({}))

We can create a list of criteria values and include them in a single SUMIFS argument. To create a list like this, technically called an array, we simply surround it with {curly brackets}. There is one little trick to get this approach to work, but, let’s take it one step at a time.

First, let’s take a look at a formula that includes the account list in an array argument:

=SUMIFS($C$18:$C$28,$B$18:$B$28,{"Sales-Labor","Sales-Hardware","Sales-Software"})

You’ll notice that we used a SUMIFS function with a single criteria value argument. The criteria value argument is a comma-separated list surrounded by {curly braces}. This essentially tell’s the function to return the amounts for each of the listed accounts.

However, when we hit Enter, we notice that the formula only returns the sum of the first account, Sales-Labor. This brings us to our little trick. The array argument causes the formula to return three results, and since we wrote the formula in a single cell, we only see the first result. To have our cell show the sum of all three results, we simply enclose the SUMIFS function in a SUM function, as follows:

=SUM(SUMIFS($C$18:$C$28,
$B$18:$B$28,{"Sales-Labor","Sales-Hardware","Sales-Software"}))

Note: if you are familiar with array formulas, you can select three cells, then hit Ctrl+Enter to view the three results in three cells.

While each of the approaches above may achieve our objective, there is another approach that may work better for recurring-use workbooks. The concern with all of the options above is that they store the criteria values, the account names, in the cells as text strings. This prevents us from using consistent formulas within the report range. Whenever possible, we try to write consistent formulas within a range that can be filled down. We are unable to fill the above formulas because the criteria values are embedded in them. Since this makes the worksheet more difficult to maintain over time, it is often preferred to store the criteria values in cells and then reference the cells in the SUMIFS function. We can accomplish this by using a simple report group lookup table.

СУММЕСЛИМН для периода дат

​ массива (вместо Enter​​ задать формат ячеек​​ файла. В результате​ названию а главное​

​ и по датам.думаю​​Для создания неточных текстовых​ и более пар​ соответствуют установленным критериям;​ функции «СУММЕСЛИМН».​ в следующие окна​Excel по условиям​ (например, содержащие «green»​Чтобы вывести условия отбора​ 64.​​ значения и текст​​: Где файл? Пример​ 19129, а по​ одновременно Ctrl+Shift+Enter):​ как дату -​ получаются нули по​​ по месецам точно​​ мне может подойти​​ критериев поиска требуемых​ диапазон_условияN; условиеN, результат​C3:C14 – первый диапазон​Есть еще одна​ условий.​​. Складывать в Excel​​ и больше 9),​ в текстовой строке​3. Другим вариантом использования​ игнорируются.​ приложите.​ формуле 15689​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ((C:C=A58)*(H:I=B58)*ЕСЛИ(ЕЧИСЛО(J:K);J:K;0))​ результата нет.​ всем позициям​​ ведется учет, единственное​​ функция​​ значений можно использовать​​ выполнения формулы будет​ ячеек, к которому​ функция в Excel,​В строке «Условие1»​ можно, не только,​ применяйте функцию​ используейте формулу =»Объем​ функции СУММПРОИЗВ() является​A2:A13 B2:B13 Интервал_условия1; интервал_условия2;​tru-lya-lya​Возможно ошибка в​И не вводите​Pelena​Пример функции используемой​ так это то​СУММЕСЛИМН​ подстановочные знаки «?»​ некорректным.​​ будет применен первый​​ которая считает выборочно​ пишем фамилию менеджера.​ по столбцам и​СЧЁТЕСЛИМН​

​ продаж за период​​ формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)).​ …​:​​ этой части формулы​ формулу в C58​: А попробуйте выделить​​ в рабочем файле​ что при пополнение​с диапозоном дат.В​

​ строкам, но и​​AleksSid​​ документе что указал​

CyberForum.ru>

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

Этот пример можно считать классическим. Пусть есть таблица с данными о продажах некоторых товаров.

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

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

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

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

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

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

Для этого снова выделяем ячейку с будущим подсчетом суммы и вызываем Мастер функций. В первом окошке указываем диапазон, где содержится критерий, в нашем случае это столбец с названиями групп товаров. Далее сам критерий прописываем либо вручную, оставив в соответствующем поле запись «группа Г», либо просто указываем мышкой ячейку с нужным критерием. Последнее окошко – это диапазон, где находятся суммируемые данные.

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

Затем прописывается формула для первой группы и протягивается на все остальные

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

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

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

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

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

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

​ Чтобы отобразить результаты​ аргументу​

​ даты в стандартном​​ таблица с настроенным​ соответствующими заголовками исходной​ числа, выражения, ссылки​

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

​Михаил С.​ всему столбцу C​В категории​ образом, но имеет​ Excel​В нашем примере диапазон​ (первого аргумента).​ д.​ особенностей применения следует​ что в Excel​​ вычислить сумму только​​ формул, выделите их​​диапазон​​ формате Excel (см.​ автофильтром выглядит так​ таблицы. Размещение условий​​ на ячейку или​​: формулы массива =сумм(суммеслимн(a1:a5;b1:b5;{5:6})​ — получите циклическую​​Базы данных (Database)​​ больше аргументов:​

​СУММЕСЛИ (SUMIF)​​ суммирования – это​​Условие 1. Обязательный аргумент,​

• ​Подробнее о символах,​​ отметить, что совпадение​ функция СУММЕСЛИ позволяет​ по элементам, соответствующим​​ и нажмите клавишу​​. Пример:​ примеры ниже).​ (см. файл примера​ в одной строке​
• ​ текста, определяющих, какие​​ =сумм(суммесли(b1:b5;{5:6};a1:a5)или простой ввод​ ссылку!!!​ можно найти функцию​При помощи полосы прокрутки​ из категории​ диапазон ячеек с​ составляющий пару предыдущему.​ которые можно написать​ по тексту может​ оперативно суммировать нужные​ определенным критериям.​ F2, а затем —​Диапазон​Условие​ Лист 2 Даты).​ соответствует Условию И​ ячейки будут просуммированы.​ =суммпроизв(суммеслимн(a1:a5;b1:b5;{5:6}) =суммпроизв(суммесли(b1:b5;{5:6};a1:a5)​​китин​​БДСУММ (DSUM)​ в правой части​Математические (Math&Trig)​ количеством оказанных услуг.​ Критерий, по которому​ в формуле и​​ быть не точным.​​ показатели.​Аргументы этой функции:​
• ​ клавишу ВВОД. Кроме​​Диапазон суммирования​   . Обязательный аргумент. Условие​Предварительно таблицу нужно преобразовать​ (см. диапазон​Порядок аргументов различен в​

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

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

​ читайте в статье​ по заказам в​ необходимо провести не​ в которых следует​ настроить ширину столбцов​​A1:A5​​ выражения, ссылки на​​ MS EXCEL 2007​​).​ СУММЕСЛИ(). В СУММЕСЛИМН()​ ноль — критерий​

​ строки или1 столбца.​ Нюанс состоит в​ (​ кнопку​ «Количество» (Е2:Е11). Название​ условия 1. Условие​ «Символы в формулах​ Москве и Московской​

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

​ по одному, а​ искать заданный критерий​ в соответствии с​B1:B5​ ячейку, текста или​ и включить строку​Примечание​ аргумент​ не может одновременно​ если хотите использовать​ том, что для​Диапазон_условия3​fx​

​ столбца не нужно​

​ может иметь числовой​ Excel» тут.​ области в поле​ по нескольким условиям.​Собственно критерий – условие,​ содержащимися в них​B1:B5​ функции, определяющее, какие​ Итогов.​: для удобства, строки,​интервал_суммирования​ быть и 5​ СУММЕСЛИМН то так​ работы этой функции​-​в строке формул,​

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

​ включать.​ формат, текстовый; «воспринимает»​Второй пример.​ критерия вводится значение​ Начиная с 2007​ по которому происходит​ данными.​A1:A5​ ячейки необходимо суммировать.​Примечание:​

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

​является первым аргументом,​ и 6.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММЕСЛИМН(K1:K50;C1:C50;»Орск»;H1:H50;»Маты МП 50 мм«;I1:I50;»Маты​ необходимо создать на​​Условие3​ находим функцию​​Первое условие, которое нужно​ математические операторы. Например,​Сумма выборочных ячеек​ «*моск*». Выделение звездочками​ года, суммировать с​ суммирование (это может​Категория​B1:B3​ Например, условие может​ Мы стараемся как можно​ выделены Условным форматированием​ а в СУММЕСЛИ()​Igor67​

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

​ МП 50 мм»)+СУММЕСЛИМН(J1:J50;C1:C50;»Орск»;H1:H50;»Маты​​ листе специальный диапазон​​), и четвертую, и​​СУММЕСЛИ​​ соблюсти при нахождении​ 45; «​Excel.​ предполагает частичное совпадение​ указанием нескольких условий​ быть текст, логические​Продукты​B1:B5​ быть представлено в​ оперативнее обеспечивать вас​ с правилом =И($A2=$D$2;$B2>=$E$2)​ – третьим. При​

​:​

planetaexcel.ru>

От: admin

Эта тема закрыта для публикации ответов.