Функция СУММИФС, как использовать функцию для суммирования нескольких условий в Excel

Функция Sumifs в Excel чрезвычайно полезна, когда вам нужно суммировать множество условий. Ниже приведены сведения о том, как использовать функцию Sumifs в Microsoft Excel .

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

• MS Excel - Урок 5: Формулы и функции Excel
• Как объединить функцию Sumif и функцию Vlookup в Excel
• 3 способа посчитать итоги в Excel
• Как исправить ошибку, из-за которой функция СУММ не добавляется в 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.

Вот пошаговые подробности:

1. Выберите ячейку H3 .
2. Введите =СУМИМФ.
3. Дважды щелкните команду СУММИФС.
4. Выберите общий диапазон как C2:C759.
5. Тип,
6. Выберите диапазон для первого условия B2:B759(значение типа 1)
7. Выберите критерии (ячейка F3со значением Water)
8. тип ,
9. Выберите диапазон для второго условия из D2:D759(Значение генерации)
10. Тип,
11. Определите критерии (ячейка G3 имеет значение 1).
12. Нажмите Ввод .

Примечание. Вы можете добавить дополнительные условия, повторив шаги с 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 и повторите формулу.

Желаю вам успехов!