Функция Sumifs в Excel чрезвычайно полезна, когда вам нужно суммировать множество условий. Ниже приведены сведения о том, как использовать функцию Sumifs в Microsoft Excel .
Функция СУММЕСЛИМ в Excel — это одна из основных функций Excel , часто используемая функция вычислений в Excel. Чтобы вычислить сумму в Excel, мы будем использовать функцию СУММ . Если мы хотим добавить определенное условие к функции итога, мы будем использовать функцию СУММЕСЛИ . В тех случаях, когда таблица данных требует суммы со многими условиями, мы должны использовать функцию СУММИФ. В статье ниже рассказывается, как использовать функцию СУММИФ в Excel.
- MS Excel - Урок 5: Формулы и функции Excel
- Как объединить функцию Sumif и функцию Vlookup в Excel
- 3 способа посчитать итоги в Excel
- Как исправить ошибку, из-за которой функция СУММ не добавляется в Excel
Оглавление статьи
- Синтаксис функции СУММЕС в Excel
- Обратите внимание при использовании функции СУММИН в Excel.
- Инструкции по использованию функции СУММЕСЛ в Excel
- Ограничения функции SUMIFS
Синтаксис функции СУММЕС в Excel
Синтаксис функции СУММЕСЛ в Excel: =СУММФ(диапазон_суммы,диапазон_критерия1,критерий1,диапазон_критерия2,критерий2,…).
Там:
- Диапазон_суммы: ячейки, которые необходимо суммировать в таблице данных, пустые значения и текстовые значения игнорируются, обязательные параметры.
- Criteria_range1: диапазон, который необходимо проверить с использованием критерия критерия 1, является обязательным значением.
- Критерий 1: условие, применяемое к диапазону критериев_1, может быть числом, выражением, ссылкой на ячейку, чтобы определить, какие ячейки в диапазоне критериев_1 будут суммироваться, а также обязательное значение.
- Criteria_range2, критерии2,…: необязательные дополнительные диапазоны и условия, максимум 127 пар критериев_диапазона, критериев.
Обратите внимание при использовании функции СУММИН в Excel.
- Для каждой ячейки в диапазоне диапазон sum_range будет суммироваться, когда все соответствующие условия, определенные как истинные для этой ячейки, будут выполнены.
- Ячейки в диапазоне sum_range, содержащие TRUE, считаются 1, ячейки, содержащие FALSE, считаются 0.
- Каждый диапазон критериев должен иметь тот же размер выборки, что и диапазон_суммы, диапазон критериев и диапазон_суммы должны иметь одинаковое количество строк и одинаковое количество столбцов.
- Критерии могут использовать знак вопроса (?) вместо одного символа, звездочку (*) вместо строки. Если условие представляет собой вопросительный знак или звездочку, перед ним необходимо ввести знак ~, значением условия является текст, заключенный в «.
- Несколько условий применяются с использованием логики И, т. е. критерии1 И критерии2 и т. д.
- Каждый дополнительный диапазон должен иметь то же количество строк и столбцов, что и общий диапазон, но диапазоны не обязательно должны быть смежными. Если вы укажете несовпадающие диапазоны, вы получите ошибку #VALUE.
- Текстовая строка в критериях должна быть заключена в кавычки (""), т.е. "apple", ">32", "jap*"
- Ссылки на ячейки в критериях нельзя заключать в кавычки, т.е. "<>
- SUMIF и SUMIFS могут обрабатывать диапазоны, но не могут обрабатывать массивы. Это означает, что вы не можете использовать другие функции, такие как ГОД, в диапазоне критериев, поскольку результатом является массив. Если вам нужна эта функция, используйте функцию СУММПРОИЗВ.
- Порядок аргументов между функциями СУММЕСЛИ и СУММЕСЛИ различен. Диапазон сумм является первым аргументом в СУММЕСЛИ и третьим аргументом в СУММЕСЛИ.
Инструкции по использованию функции СУММЕСЛ в Excel
Используйте подстановочные знаки
Подстановочные знаки, такие как '*' и '?' может использоваться в аргументе критерия при использовании функции СУММИФ. Использование этих подстановочных знаков поможет вам найти похожие, но не точные совпадения.
Звездочка (*) — соответствует любой строке символов, которую можно использовать после, до или вместе с окружающими критериями, чтобы разрешить частичное использование критериев поиска.
Например, если вы примените следующие критерии в функции СУММИФ:
- N * — подразумевает все ячейки в диапазоне, начинающиеся с N.
- * N — подразумевает все ячейки диапазона, заканчивающиеся на N.
- *N* — ячейки, содержащие N
Вопросительный знак (?) — соответствует любому отдельному символу. Предположим, вы применяете N?r в качестве критерия. "?" здесь заменит один символ. Н?р будет соответствовать Северу, Нору и т. д. Однако Имя не будет учитываться.
Что делать, если данные содержат звездочку или настоящий вопросительный знак?
В этом случае вы можете использовать тильду (~). В этом случае вам нужно ввести «~» перед знаком вопроса.
Используйте именованные диапазоны с функцией СУММИФ.
Именованный диапазон — это описательное имя набора ячеек или диапазона ячеек на листе. Вы можете использовать именованные диапазоны при использовании функции СУММИФ.
Пример функции СУММЕСЛ в Excel
Мы рассчитаем общую сумму с помощью таблицы данных ниже с рядом различных условий.

Пример 1 : Рассчитайте общее количество продукции, которую сотрудник Hoai продает по цене менее 400 000 донгов.
В поле ввода результата мы вводим формулу =СУММФ(D2:D7,C2:C7,"Hoai",E2:E7,"<400000") и затем="" нажмите="">
Там:
- D2:D7 — это область, в которой необходимо рассчитать общую сумму.
- C2:C7 — это диапазон значений условия имени сотрудника.
- «Хоай» — это условие имени сотрудника, которому необходимо посчитать количество предметов, содержащихся в области C2:C7.
- E2:E7 — состояние предмета.
- «400 000» — это условие для размещения предметов в областях E2:E7.
Результатом является общее количество элементов, как показано ниже.
Пример 2 : Подсчитать общее количество товаров, проданных российскими сотрудниками с серийным номером <>
В поле ввода результата вводим формулу =СУММИФ(D2:D7,C2:C7,"=Россия",A2:A7,"<5"), а затем="" нажмите="" Enter.="" в = "" name="" умножение="" table="" "nga"="" enter=""sign="или" удалить="" знак="даже">
В результате получаем общее количество проданных российскими сотрудниками товаров с серийными номерами < 5="" что составляет 550 шт.="">
Пример 3 : Подсчитайте общее количество продукции, проданной российскими сотрудниками, без учета шарфовой продукции.
В поле ввода результата вводим формулу =СУММИФ(D2:D7,C2:C7,«Россия»,B2:B7,«<>шарф»). В котором знак <> используется для исключения определенного объекта из области данных в качестве условия.
Результатом является общее количество продуктов, как показано ниже.
Пример 4:
Рассчитайте сумму всех характеристик для покемонов водного типа 1-го поколения:
Условием здесь является то, что Вода и Генерация равны 1.
Примечание. Полный набор данных продолжается после строки 14 вплоть до строки 759.
Вот пошаговые подробности:
- Выберите ячейку H3 .
- Введите =СУМИМФ.
- Дважды щелкните команду СУММИФС.
- Выберите общий диапазон как
C2:C759. - Тип
, - Выберите диапазон для первого условия
B2:B759(значение типа 1) - Выберите критерии (ячейка
F3со значениемWater) - тип ,
- Выберите диапазон для второго условия из
D2:D759(Значение генерации) - Тип
, - Определите критерии (ячейка G3 имеет значение 1).
- Нажмите Ввод .
Примечание. Вы можете добавить дополнительные условия, повторив шаги с 9 по 12, прежде чем нажимать Enter.
Эта функция теперь вычисляет сумму общих характеристик для водных покемонов 1-го поколения. Эту функцию можно повторить для будущих поколений, чтобы сравнить их.
Выше приведены несколько примеров использования функции СУММЕСЛИМ для расчета общего значения со многими комбинированными условиями. Мы должны правильно расположить диапазоны и сопутствующие условия, чтобы Excel мог распознать формулу функции для расчета.
Ограничения функции SUMIFS
Функция СУММИФС имеет некоторые ограничения, о которых вам следует знать:
- Условия в SUMIFS объединяются с помощью логики AND. Другими словами, чтобы ячейка была включена в итог, все условия должны быть TRUE. Чтобы суммировать ячейки с помощью логики ИЛИ, в простых случаях можно использовать альтернативное решение.
- Функция СУММИФС требует фактических диапазонов для всех аргументов диапазона; вы не можете использовать массив. Это означает, что вы не можете делать такие вещи, как извлечение года из даты внутри функции СУММИФ. Чтобы изменить значения, которые появляются в аргументе диапазона перед применением критериев, функция СУММПРОИЗВ является гибким решением.
- SUMIFS не чувствителен к регистру. Для суммирования значений на основе условия с учетом регистра можно использовать формулу, основанную на функции СУММПРОИЗВ с функцией ТОЧНО.
Самый распространенный способ обойти вышеуказанные ограничения — использовать функцию СУММПРОИЗВ. В текущих версиях Excel другой вариант — использовать новые функции BYROW и BYCOL.
Как исправить ошибку #VALUE в функции СУММИФ Excel
Проблема: формула относится к ячейкам закрытой книги.
Функция СУММЕСЛИ, которая ссылается на ячейку или диапазон ячеек в закрытой книге, приведет к ошибке #ЗНАЧ!.
Примечание. Это распространенная ошибка. Вы также часто сталкиваетесь с этим сообщением при использовании других функций Excel, таких как СЧЁТЕСЛИ, СЧЁТЕСЛИ, СЧЁТПУСТО...
Решение:
Откройте книгу, содержащую эту формулу, нажмите F9, чтобы обновить формулу. Вы также можете решить проблему, используя функции СУММ и ЕСЛИ в формуле массива.
Проблема: строка критериев длиннее 255 символов.
Функция СУММИФ возвращает неправильные результаты при попытке объединить строки длиной более 255 символов.
Решение:
Если возможно, сократите строку. Если это невозможно, используйте функцию СЦЕПИТЬ или оператор &, чтобы разделить значение на несколько строк. Например:
=SUMIF(B2:B12,"long string"&"another long string")
Проблема. В SUMIFS аргумент диапазон_критериев несовместим с аргументом диапазон_суммы.
Аргумент диапазона всегда должен быть одним и тем же в SUMIFS. Это означает, что аргументы «диапазон_критерия» и «диапазон_суммы» должны относиться к одинаковому количеству строк и столбцов.
В следующем примере формула возвращает общий ежедневный объем продаж яблок в Бельвью. Однако аргумент sum_range (C2:C10) не соответствует количеству строк и столбцов в диапазоне критериев (A2:A12 и B2:B12). Использование формулы =SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)приведет к ошибке #VALUE.
Решение:
Измените sum_rangeна C2:C12 и повторите формулу.
Желаю вам успехов!








