Суммесли (sumif)

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

​ не может одновременно​​ (для наглядности я​​ способом, который будет​​ то суммирование будет​​ темами на портале​​ строке #ЗНАЧ!.​​является первым, а​
​из поиска исключаются​ таблице, щелкните их​ быть и 5​ 0-й и 1-ц​ всю работу формулы​ математическими символами должны​ показан в следующем​ суммирования. СУММЕСЛИМН удобна​ производиться по диапазону​ в Excel, примеры»​Получились такие данные.​
​ пользовательских предложений для​ в статье Функции​210 000 ₽​Аргумент​ с несколькими условиями,​​ в функции СУММЕСЛИ —​ бананы (​​ правой кнопкой мыши​​ и 6.​​ не умножается на​​ продемонстрирую так, как-будто​​ быть указаны в​​ примере.​ для работы с​ ячеек, указанному в​ здесь. В этой​Второй пример.​
​ Excel.​ СУММЕСЛИ, СЧЁТЕСЛИ и​=СУММЕСЛИ(A2:A5;»>» &C2;B2:B5)​диапазон_суммирования​ используйте функцию СУММЕСЛИМН.​ третьим. Этот момент​»<> Бананы»​ и выберите команду​Igor67​ диапазон суммирования, а​
​ вычисления происходят на​ кавычках.​Пример 3. Используя таблицу​ объемными таблицами, содержащими​ аргументе​ статье мы рассмотрели,​Пример функции «СУММЕСЛИ» в​Рассмотрим одну из​ СЧИТАТЬПУСТОТЫ возвращают ошибку​
​Сумма комиссионных за имущество,​может не совпадать​СУММЕСЛИ(диапазон; условие; )​ часто является источником​). Кроме того, функция​Копировать​:​ отбирается с помощью​ листе, а не​​Максимальная длина условия, переданного​ из второго примера​ большое число полей​Диапазон​ как использовать символы,​Excel по дате.​ популярных​ #ЗНАЧ!.​ стоимость которого превышает​ по размерам с​Аргументы функции​ проблем при использовании​ выполняет поиск имени​. На новом листе​Михаил С.,​ функции ЕСЛИ.​ в памяти), то​ в качестве аргумента​ определить суммарной прибыли​ данных. Например, если​​.​ для поиска конкретных​Теперь посчитаем сумму​функций в​Решение:​ значение в ячейке​ аргументом​СУММЕСЛИ​ данных функций.​»Артем»​
​ щелкните правой кнопкой​ну да, что​МИНУСЫ: Формулы массива​ массивы выглядели бы​ условие1 (условие2 и​
​ от поставок товара​ требуется рассчитать общую​​Рассмотрим случай, когда аргумент​ данных.​ за определенные даты.​Excel «СУММЕСЛИ»​Откройте книгу, указанную​ C2.​диапазон​​описаны ниже.​При копировании и изменении​​в диапазоне ячеек,​ мыши ячейку A1​​ то не досмотрел​​ при вычислениях на​​ так:​​ т. д.) не​ №1 в Китай​ сумму средств, полученных​Диапазон_суммирования​Если нужно указать​ Можно в формуле​. Эта функция выбирает​ в формуле, и​490 000 ₽​. При определении фактических​Диапазон​ этих похожих формул​ заданных аргументом​ и в разделе​​ ведь там 1​ больших диапазонах данных​Очевидно что если​ должна превышать 255​ и Грузию на​ в результате поставки​​опущен. В этом​ не одно условие​​ указать эту дату.​​ и складывает данные​​ нажмите клавишу F9,​​Скопируйте образец данных из​​ ячеек, подлежащих суммированию,​​   . Обязательный аргумент. Диапазон​ нужно следить за​Диапазон_условия2​Параметры вставки​ диапазон суммирования и​ заметно увеличивают время​​ например, D2=Карандаши, то​ символов.​ протяжении всего периода​ определенного типа товара​ случае суммирование будет​ суммирования, а несколько,​ А можно в​
​ из таблицы по​​ чтобы обновить формулу.​ приведенной ниже таблицы​ в качестве начальной​​ ячеек, оцениваемых на​ правильным порядком аргументов.​​(C2:C9). Затем функция​​выберите команду​

excelworld.ru>

Сумма столбцов из нескольких таблиц.

Как в Экселе посчитать сумму столбца, если таких столбцов несколько, да и сами они находятся в нескольких разных таблицах?

Для получения итогов сразу по нескольким таблицам также используем функцию СУММ и структурированные ссылки. Такие ссылки появляются при создании в Excel «умной» таблицы.

При создании её Excel назначает имя самой таблице и каждому заголовку колонки в ней. Эти имена затем можно использовать в выражениях: они могут отображаться в виде подсказок в строке ввода.

В нашем случае это выглядит так:

Прямая ссылка	Структурированная ссылка (Имя таблицы и столбца)
B2:B21	Таблица2

Для создания «умной» таблицы выделим диапазон A1:B21 и на ленте «Главная» выбираем «Форматировать как таблицу».

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

Также в нашем случае не принципиально, где именно располагаются в вашем файле Excel эти данные

Даже не важно, что они находятся на разных листах – программа все равно найдет их по имени

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

И если будут внесены какие-то изменения или добавлены цифры, то все пересчитается автоматически.

Примечание: итоговая строчка в таблице должна быть включена. Если вы отключите её, то выражение вернет ошибку #ССЫЛКА.

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

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

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

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

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

Также рекомендуем:

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

​ или применяется к​ с несколькими условиями,​ проблем при использовании​

Синтаксис

​ заданных аргументом​Использовать форматы конечных ячеек​

• ​Функция СУММЕСЛИМН — одна из​ условием в числовой​

• ​ решается одной формулой:​Если условий больше одного​

Примеры

​ условиям значения в​	​Продавец​	​ нескольким условиям. Например,​
​ окончанием периода, и​	​ Enter, как обычно,​	​ то функция​
​ 68.​	​ определенном городе с​	​ функция СУММЕСЛИМН, которая​
​Двигаем поле условий​	​ можно, не только,​	​Рассмотрим,​
​(COUNT).​	​=СУММЕСЛИ(A2:A5;»>» &C2;B2:B5)​	​ по размерам с​
​описаны ниже.​	​ правильным порядком аргументов.​	​ диапазоне ячеек, заданном​
​5​	​ с помощью этой​	​ то, в формулу​
​ а​	​СУММЕСЛИ (SUMIF)​	​По такому же принципу​
​ учетом вида услуги.​	​ позволяет при нахождении​	​ полосой прокрутки, пишем​
​ по столбцам и​	​как применять символы в​
​=СЧЁТ(A1:A5)​Сумма комиссионных за имущество,​	​ аргументом​Диапазон​​Одинаковое количество строк и​​ аргументом​Яблоки​​ функции можно найти​​ приходится подставлять его​Ctrl + Shift +​​не поможет, т.к.​​ можно сделать выпадающий​​Как использовать функцию СУММЕСЛИМН​​ суммы учитывать сразу​ следующее условие.​ строкам, но и​ формулах​​=COUNT(A1:A5)​​ стоимость которого превышает​диапазон​   . Обязательный аргумент. Диапазон​​ столбцов для аргументов,​​Диапазон_суммирования​Артем​​ число всех розничных​​ вручную ((((​ Enter​ не умеет проверять​ список для услуг.​ в Excel:​​ несколько значений. В​​В строке «Диапазон​
​ выбирать для сложения​Excel, чтобы посчитать сумму​	​Для подсчета ячеек по​ значение в ячейке​. При определении фактических​ ячеек, оцениваемых на​ задающих диапазоны ячеек​(A2:A9). Результат — 30.​​4​​ продавцов, (1) проживающих​​Подскажите пож-та,​​- тогда Excel​ больше одного критерия.​​Имеем таблицу по продажам,​​Вызываем «Мастер функций». В​ самом названии функции​​ условий3» пишем диапазон​​ данные из таблицы​ по условию, выборочно.​​ одному критерию (например,​​ C2.​ ячеек, подлежащих суммированию,​ соответствие условиям. Ячейки​Аргумент​Проблема​​Яблоки​​ в одном регионе,​

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

​как сделать по-человечески?​	​ воспримет ее как​
​ Поэтому начиная с​ например, следующего вида:​	​ категории «Математические» находим​ заложено ее назначение:​ С2:С8.​ по разным условиям.​Символы в формулах​​ больше 9), используйте​​490 000 ₽​
​ в качестве начальной​ в каждом диапазоне​Диапазон_условия​Описание​​Ольга​​ (2) чей доход​заранее спасибо​	​ формулу массива и​ версии Excel 2007​Задача​​ СУММЕСЛИМН. Можно поставить​​ сумма данных, если​В строке «Условие3»​Как установить простое​ помогают выбрать из​ ​ функцию​​Скопируйте образец данных из​​ используется верхняя левая​ должны содержать числа,​должен иметь то​Вместо ожидаемого результата отображается​15​ превышает установленный уровень.​

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

​alx74​	​ сам добавит фигурные​
​ в набор функций​	​: просуммировать все заказы,​ в ячейке знак​ совпадает множество условий.​ пишем – «яблоки».​​ условие для подсчета​​ таблицы конкретные данные.​СЧЕТЕСЛИ​ приведенной ниже таблицы​ ​ ячейка аргумента​ имена, массивы или​ же количество строк​ 0 (нуль).​Артишоки​Это видео — часть учебного​: А так не​ скобки. Вводить скобки​ была добавлена функция​ ​ которые менеджер Григорьев реализовал​ «равно» и начать​Аргументы функции СУММЕСЛИМН:​Нажимаем «ОК». Получился такой​ данных, смотрите в​ Как использовать функцию​(COUNTIF).​
​ и вставьте его​диапазон_суммирования​	​ ссылки на числа.​ и столбцов, что​Если выполняется поиск текстовых​Артем​​ курса Усложненные функции​​ работает? По-моему, должно​ с клавиатуры не​СУММЕСЛИМН (SUMIFS)​ для магазина «Копейка».​ вводить название функции.​Диапазон ячеек для нахождения​ ​ результат.​ статье «Функция «ЕСЛИ»​ «СУММЕСЛИ», смотрите в​=СЧЁТЕСЛИ(A1:A5;»>9″)​
​ в ячейку A1​, а затем суммируются​ Пустые ячейки и​	​ и аргумент​​ значений, например имени​​3​ ЕСЛИ.​ сработать, попробуйте.​ надо. Легко сообразить,​​- в ней​​Если бы в нашей​

​ Excel покажет список​

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

​ в Excel».​ статье «Функция «СУММЕСЛИ»​=COUNTIF(A1:A5,»>9″)​ нового листа Excel.​ ячейки части диапазона,​ ячейки, содержащие текстовые​Диапазон_суммирования​

support.office.com>

Стандартное использование СУММПРОИЗВ

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

=СУММПРОИЗВ(Массив1; Массив2; … )

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

Количество диапазонов, которые можно использовать, ограничено 255 штуками. Но этого более, чем достаточно. Главное требование – они должны быть одного размера.

7

По сути, функция аналогична такому выражению.

=B2*C2+B3*C3+B4*C4+B5*C5

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

Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики ( ячейка D 2 ), у которых остаток ящиков на складе >=6 ( ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

• B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
• A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
• D2 и “>=”&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.

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

2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);–(B2:B13>=E2)) Разберем подробнее использование функции СУММПРОИЗВ() :

• Результатом вычисления A2_A13=D2 является массив Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в Строке формул A2_A13=D2 , а затем нажав F9 ;
• Результатом вычисления B2:B13 является массив, т.е. просто значения из столбца B ;
• Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является . При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
• Разберем второе условие: Результатом вычисления –( B2:B13>=E2) является массив . Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
• Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .

4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) похожа на вышеупомянутую формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER

5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует предварительного создания таблицы с условиями (см. статью про функцию БДСУММ() ). Заголовки этой таблицы должны в точности совпадать с соответствующими заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И (см. диапазон D14:E15 ).

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

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

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

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

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

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

На сегодня все. Всех благ и до новых встреч на statanaliz.info.

Нахождение суммы произведений элементов массивов

Теперь давайте более подробно раскроем, как с помощью функции можно находить сумму произведений элементов массивов. 

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

Эта функция выполняет поэлементные операции со всеми диапазонами (пусть их будет даже 200), которые входят в состав массива. 

Обязательное требование к массивам – наличие одинаковых размерностей. Если массивы будут содержать разное количество элементов, будет возвращена ошибка #ЗНАЧ!.

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

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

Рассмотрим все возможные ситуации применения одноименной функции.

Общий вид

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

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

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

=СУММЕСЛИ(C3:C12;»одежда»;F3:F12), где:

• C3:C12 – тип одежды;
• «одежда» – критерий;
• F3:F12 – интервал суммирования.

В результате получили 180. Функция проработала все указанные нами условия и выдала корректный результат. Также можно повторить подобные манипуляции для других типов товара, сменив критерий «одежда» на какой-то другой.

С несколькими условиями

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

=СУММЕСЛИМН(F3:F12;C3:C12;»одежда»;E3:E12;50), где:

• F3:F12 – диапазон суммирования;
• «одежда» и 50 – критерии;
• C3:C12 и E3:E12 – диапазоны типов товара и стоимости единицы соответственно.

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

С динамическим условием

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

Для вставки новой строки нужно нажать ПКМ на интересующей ячейке и выбрать «Вставить» – «Строку».

Далее просто введите новые данные или скопируйте их с другого места. Итоговое значение соответственно изменится. Аналогичные трансформации происходят и при редактировании или удалении строк.

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

Суммирование по пустым и непустым ячейкам.

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

Критерии
Описание

Пустые ячейки
“=”
Суммируйте числа, соответствующие пустым, которые не содержат абсолютно ничего – ни формулы, ни строки нулевой длины.
=СУММЕСЛИМН(C2:C10;A2:A10;”=”;B2:B10,”=”) Суммируйте в C2:C10, если соответствующие ячейки в столбцах A и B абсолютно пусты.

«»
Суммируйте числа, соответствующие «визуально» пустым, включая те, которые содержат пустые строки, возвращаемые какой-либо другой функцией Excel (например, ячейки с формулой вроде = “”).
=СУММЕСЛИМН(C2:C10;A2:A10;””;B2:B10,””)   Суммируйте в C2:C10 с теми же параметрами, что и в приведенной выше формуле, но с пустыми строками.
Непустые ячейки
“”
Суммируйте числа, соответствующие непустым, включая строки нулевой длины.
=СУММЕСЛИМН(C2:C10;A2:A10;””;B2:B10,””) Суммируйте в C2: C10, если соответствующие ячейки в столбцах A и B не пусты, включая ячейки с пустыми строками.

Суммируйте числа, соответствующие непустым, не включая строки нулевой длины.
=СУММ(C2:C10) – СУММЕСЛИМН(C2:C10;A2:A10;””;B2:B10,””) или{=СУММ((C2:C10)*(ДЛСТР(A2:A10)>0)*(ДЛСТР(B2: B10)>0))} Если в столбцах A и B содержится текст ненулевой длины, тогда соответствующее число из C складывается. Внимание! Это формула массива! Фигурные скобки вводить не нужно!

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

По покупателю «Красный» рассчитаем количество товара в невыполненных заказах. Для этого в столбце B ищем соответствующее название клиента, а в F – пустую ячейку. Если оба требования совпадают, складываем количество товара из столбца D.

или

Каждое из этих выражений дает верный результат – 144 единицы в заказе от 4 февраля.

Сумма нескольких условий.

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

Если мы просто добавим второй критерий в I3, и вместо I2 используем область I2:I3, то расчет будет неверным, поскольку в C2:C21 будем искать товар, в названии которого есть И «черный», И «молочный» одновременно. Ведь таких просто нет.

Поэтому первый вариант расчета таков:

Просто складываем выполненные заказы сначала по первому, а затем по второму наименованию.

Второй вариант: используем элемент массива критериев и функцию СУММ.

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

Ещё примеры расчета суммы:

голоса

Рейтинг статьи

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

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

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

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

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

Сумма, если ячейки содержат либо x, либо Y

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

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

Одно из решений-использовать функцию СУММПРОИЗВ с ЕЧИСЛО + ПОИСК или НАЙТИ.

Эта формула основана на формуле, что находит текст внутри ячейки:

При заданном диапазоне ячеек, этот фрагмент будет рассчитывать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы делаем это дважды (один раз для «кошки» и один раз для «крысы»), мы получим два массива.

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

Нам нужно добавить эти цифры, но мы не хотим, чтобы удвоился счет. Поэтому мы должны убедиться, что любое значение больше нуля, всего лишь раз пересчитать. Чтобы сделать это, мы превращаем все значения Истина или Ложь, проверяя массив с «>0». Это рассчитывает Истина или Ложь:

Которые мы затем преобразовываем в 1/0 с помощью двойного отрицания (—):

Параметр чувствителен к регистру

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

СУММПРОИЗВ как формула массива

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

=СУММПРОИЗВ(–ЕПУСТО(D2:D23)) – в этом случае ищутся пустые ячейки в диапазоне, подсчет которых осуществляется потом. 

=СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) – здесь осуществляется отбор значений, которые больше среднего арифметического, после чего подсчитывается их сумма. 

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

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

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

Начните запись функции с ее обозначения точно так же, как это уже было показано выше.

В первую очередь введите диапазон надписей, поставьте ; и задайте условие. Тогда это выражение в синтаксическом формате обретет примерно такой вид: A2:A25;«Сентябрь»;.

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

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

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