Суммировать во многих условиях Excel

Обновлено: 28.04.2024

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

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

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

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

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

=СУММЕСЛИ(диапазон, критерии1, диапазон_суммы) + СУММЕСЛИ(диапазон, критерии2, диапазон_суммы)

< /p>

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

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

Синтаксис функции СУММЕСЛИМН:

СУММЕСЛИМН(сумма_диапазон, критерий_диапазон1, критерий1, критерий_диапазон2, критерий2. )

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

< /p>

=СУММЕСЛИМН(D2:D22,E2:E22,">="&G2,E2:E22,"

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

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

Константа массива – это набор нескольких критериев, заключенных в фигурные скобки <> в формуле, например

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

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

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

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

Наконец, функция СУММ суммирует эти результаты массива, чтобы получить общую цифру, как показано ниже;

< /p>

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

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

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

Одним из решений является предоставление нескольких критериев в константе массива следующим образом:

Это приведет к тому, что СУММЕСЛИМН вернет два результата: количество "завершено" и количество "ожидающих выполнения" в виде массива результатов, подобного этому:

Чтобы получить окончательную сумму, мы заключаем СУМММЕСЛИМН в СУММ. Функция СУММ суммирует все элементы массива и возвращает результат.

С подстановочными знаками

При необходимости в критериях можно использовать подстановочные знаки. Например, чтобы суммировать элементы, которые содержат «красный» или «синий» в любом месте диапазона критерии, вы можете использовать:

Добавление другого критерия ИЛИ

Вы можете добавить еще один критерий в эту формулу, но вам потребуется использовать массив из одного столбца для одного критерия и массив из одной строки для другого. Так, например, чтобы суммировать заказы, которые являются «Завершенными» или «Ожидающими» для «Энди Гарсии» или «Боба Джонса», вы можете использовать:

Обратите внимание на точку с запятой во второй константе массива, которая представляет вертикальный массив. Это работает, потому что Excel «сопоставляет» элементы в двух константах массива и возвращает двумерный массив результатов. При большем количестве критериев вы захотите перейти к формуле, основанной на СУММПРОИЗВ.

Ссылки на ячейки для критериев

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

Где диапазон1 — это диапазон суммы, диапазон2 — это диапазон критериев, а диапазон3 содержит критерии на листе. С двумя критериями ИЛИ вам нужно будет использовать горизонтальные и вертикальные массивы.

Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter.

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

• sum_range – суммируемый диапазон.
• range1 — первый диапазон для оценки.
• criteria1 — критерии для диапазона 1.
• range2 – [необязательно] второй диапазон для оценки.
• criteria2 – [необязательно] критерии для диапазона2.

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

Критерии могут включать логические операторы (>, ,=) и подстановочные знаки (*,?) для частичного совпадения. Критерии также могут быть основаны на значении из другой ячейки, как описано ниже.

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

Если вам нужно манипулировать значениями, которые появляются в аргументе range (т. е. извлекать год из дат для использования в критериях), см. функции СУММПРОИЗВ и/или ФИЛЬТР.

Примеры

Для одного условия общая схема СУММЕСЛИМН такова:

Когда ячейки в range1 соответствуют условию1, соответствующие ячейки в sum_range суммируются. Для двух условий шаблон таков:

Критерии уведомления указаны в парах диапазон/критерий. Когда ячейки в диапазоне1 соответствуют критерию1, и ячейки в диапазоне2 соответствуют критерию2, соответственно ячейки в sum_range суммируются. Тот же шаблон расширен для обработки большего количества условий.

Пример рабочего листа

На показанном листе есть две формулы СУММЕСЛИМН. В первом примере (I5) функция СУММЕСЛИМН возвращает сумму значений в столбце F, где цвет столбца C — «красный». Во втором примере (I6) СУММЕСЛИМН суммирует значения в столбце F, когда цвет "красный" и штат Техас (TX):

Обратите внимание, что знак равенства (=) не требуется при построении критерия "равно". Также обратите внимание, что СУММЕСЛИМН не чувствителен к регистру; вы можете использовать «красный» или «красный», а также «TX» или «tx».

Примечания

Функция СУММЕСЛИ поддерживает логические операторы Excel (например, "=",">",">=" и т. д.), поэтому вы можете использовать их по своему усмотрению в своих критериях. В этом случае мы хотим сопоставить суммы меньше 1000, а "диапазон критериев" равен

.

Первый диапазон (D4:D11) — это ячейки для суммирования, называемые "диапазоном суммирования". Критерии поставляются парами. (диапазон/критерий). Первая пара критериев (B4:B11 / "синий"). Это означает, что ячейки в B4:B11 должны иметь значение "синий".

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «ноль или более символов», а вопросительный знак (?) означает «любой один символ». Подстановочные знаки позволяют создавать такие критерии, как «начинается с», «заканчивается на», «.

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

Время в Excel — это числа, и их можно суммировать, как и другие числовые значения. В этом примере F4:G7 — это сводная таблица, показывающая общее время регистрации в каждом из трех состояний: «Ожидание», «Работа» и «Автономный режим». Эти значения.

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

Функция СУММЕСЛИ поддерживает логические операторы Excel (например, "=",">",">=" и т. д.), поэтому вы можете использовать их по своему усмотрению в своих критериях. В этом случае мы хотим сопоставить суммы больше 1000, а "диапазон критериев" равен

.

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

Первым аргументом для СУММЕСЛИ всегда является диапазон для суммирования ("диапазон_суммы"), а критерии предоставляются в виде одной или нескольких пар "диапазон/критерий". В этом примере диапазон сумм – это именованный диапазон с именем "сумма" (E3:E2931) и.

Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или несколько символов», а вопросительный знак (?) означает «любой один символ». Эти подстановочные знаки позволяют создавать такие критерии, как "начинается с", "заканчивается".

В этом примере диапазон суммы — это именованный диапазон «время», введенный как время Excel в формате чч:мм. Первый критерий внутри СУММЕСЛИМН включает даты, которые больше или равны дате недели в столбце F: дата.

Функция СУММЕСЛИМН может обрабатывать несколько критериев при вычислении суммы. В этом случае СУММЕСЛИМН настроен с диапазоном суммы для всех сумм: =СУММЕСЛИМН($D$5:$D$104 Первый критерий указывает диапазон критериев, который.

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

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

Похожие видео

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

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

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

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

< /p>

< /p>

Как суммировать один или несколько критериев в Excel?

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

Суммировать значения ячеек по одному критерию в Excel

Во-первых, я приведу вам для справки синтаксис SUMIF:

СУММЕСЛИ(диапазон, критерии, [сумма_диапазон])

• Диапазон: диапазон ячеек для оценки по вашим критериям.
• Критерии: условие, которое вы хотите использовать;
• Sum_range: диапазон ячеек для суммирования, если условие выполнено.

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

(1.) Сумма количества, когда продукт «KTE»:

=СУММЕСЛИ(A2:A12,E2,C2:C12) и нажмите клавишу Enter, чтобы получить результат (A2:A12 – это ячейки диапазона, содержащие критерии, E2 – критерий, по которому вы хотите суммировать значения, основанные на , C2:C12 относится к диапазону, который вы хотите суммировать) см. снимок экрана:

(2.) Суммируйте количество всех товаров, кроме «KTE»:

=СУММЕСЛИ(A2:A12,"<>KTE",C2:C12) , затем нажмите клавишу Enter, чтобы просуммировать все количества продуктов, за исключением KTE, (A2:A12 – это ячейки диапазона, содержащие критерии, <>KTE критерий, который исключает KTE, C2:C12 относится к диапазону, который вы хотите суммировать), см. снимок экрана:

(3.) Суммируйте количество больше 250:

=СУММЕСЛИ(C2:C12,">250") и нажмите клавишу Enter, чтобы получить результат (C2:C12 относится к диапазону, который вы хотите суммировать, >250 – это критерий, который вам нужен, вы также можете изменить меньше 250, как вам нужно), см. скриншот:

(4.) Используйте функцию СУММЕСЛИ+СУММЕСЛИ+… с несколькими критериями ИЛИ, просуммируйте количество KTE и KTW:

=СУММЕСЛИ(A2:A12,"KTE",C2:C12) + СУММЕСЛИ(A2:A12,"KTW",C2:C12) ( A2:A12 - это ячейки диапазона, которые содержат критерии, KTE и KTW критерии, на основе которых вы хотите суммировать значения, C2:C12 относится к диапазону, который вы хотите суммировать), а затем нажмите клавишу Enter, чтобы получить результат:

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

Синтаксис СУММЕСЛИМН следующий:

СУММЕСЛИМН(сумма_диапазон, критерий_диапазон1, критерий1,[критерий_диапазон2, критерий2]. )

• Sum_range: диапазон ячеек для суммирования;
• Criteria_range1: первый диапазон, содержащий критерии;
• criteria1: первое условие, которое должно быть выполнено;
• диапазон_критериев2, критерии2, . это дополнительные диапазоны и связанные с ними критерии.

(1.) Просуммируйте количество, где продукт — KTE, а имя — Джеймс:

Введите следующую формулу: =СУММЕСЛИМН(C2:C12,A2:A12,"KTE",B2:B12,"Джеймс") ,(C2:C12 – диапазон ячеек, который необходимо суммировать, A2:A12 , KTE - первый диапазон и критерий критериев, B2:B12, Джеймс, - второй диапазон и критерий критериев), а затем нажмите клавишу Enter, см. снимок экрана:

Здесь также может помочь формула массива: =SUM((A2:A12="kte")*(B2:B12="James")*C2:C12) , и вам нужно нажать Ctrl + Shift + Введите ключи вместе после ввода формулы.

(2.) Суммируйте количество, где продукт – KTE, а – не James

Введите следующую формулу: =СУММЕСЛИМН(C2:C12,A2:A12,"KTE",B2:B12,"<>Джеймс") (C2:C12 – диапазон ячеек, который нужно суммировать, A2:A12 , KTE). — первый диапазон и критерий критериев, B2:B12, <>James — второй диапазон и критерий критериев), затем нажмите клавишу Enter, чтобы получить результат, см. снимок экрана:

Другая формула массива также может завершить эту операцию: = SUM((A2:A12="kte")*(B2:B12<>"Джеймс")*C2:C12) , но вы должны нажать Ctrl + Shift + Enter клавиши после ввода этой формулы.

(3.) Суммируйте количество, где продукт равен KTE, и количество больше 150

Можно использовать следующую формулу СУММЕСЛИМН: =СУММЕСЛИМН(C2:C12,A2:A12,"KTE",C2:C12,">150") (C2:C12 – диапазон ячеек, который необходимо суммировать, A2:A12). , KTE — первый диапазон критериев и критерийj, C2:C12, >150 — второй диапазон критериев и критерий), а затем нажмите клавишу Enter, чтобы вернуть результат.

Вы также можете использовать формулу массива СУММ, чтобы получить нужный результат, =СУММ((A2:A12="kte")*(C2:C12>150)*C2:C12), не забудьте нажать Ctrl + Shift + Введите ключи после ввода этой формулы.

(4.) Суммируйте количество от 100 до 200

Чтобы суммировать значения между двумя числами, используйте следующую формулу: =СУММЕСЛИМН(C2:C12,C2:C12,">100",C2:C12," ( C2:C12 – это диапазон ячеек, который необходимо суммировать, C2:C12 , >100 - это первый диапазон критериев и критерии, C2:C12, =SUM((C2:C12>100)*(C2:C12 , не забудьте одновременно нажать клавиши Ctrl + Shift + Enter, чтобы получить правильный результат.

Читайте также:

  
• Как открыть XML в AutoCad
  
• Как удалить системные приложения в Honor
  
• Биография конфигурации ПК, что это такое
  
• Умный репетитор что это за программа и нужна ли она на телефоне
  
• Токен Сбербанка не открывается в браузере по умолчанию