Как использовать sumif/sumifs с логикой или?

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

Просто так, СУММЕСЛИ ИЛИ. Это усовершенствованная формула, которая помогает повысить гибкость СУММЕСЛИ. Да, вы также можете использовать SUMIFS. Но прежде чем использовать его, позвольте мне рассказать вам одну вещь о SUMIF.

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

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

Поскольку он работает с логикой AND, для включения ячейки должны быть соблюдены все критерии. Когда вы объединяете логику ИЛИ с SUMIF/SUMIFS, вы можете добавлять значения, используя одновременно два разных критерия.

Без лишних слов, давайте изучим эту удивительную формулу.

Действительно ли нам нужна логика ИЛИ? Пример?

Позвольте мне показать вам пример, где нам нужно добавить логику ИЛИ в SUMIFS или SUMIF.

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

В этих данных у вас есть два типа продукции (дефектная и поврежденная), которую вы должны вернуть нашему поставщику.

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

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

ОК, давайте применим OR с SUMIFS

Прежде чем начать, загрузите этот образец файла отсюда.   продолжение следует.

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

 =SUM(SUMIFS(C2:C21,B2:B21,{"Damage","Faulty"}))

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

 =SUM(SUMIF(B2:B21,{"Damage","Faulty"},C2:C21))

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

Как работает эта формула?

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

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

1. Прежде всего, нужно понять, что вы использовали в этой формуле два разных критерия, используя концепцию массива. Узнайте больше о доске здесь .
2. Во -вторых , когда вы указываете два разных значения с помощью массива, SUMIFS должен искать два значения отдельно.
3. В- третьих , даже после использования формулы массива СУММИФС не может вернуть сумму двух значений в одной ячейке. Вот почему вам нужно объединить его с функцией СУММ.

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

Это будет работать одинаково с SUMIF и SUMIFS.

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

Использование динамического диапазона критериев

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

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

И для этого вам нужно внести всего лишь два небольших изменения в вашу формулу.

1. Во-первых, вместо использования фигурных скобок вам следует использовать именованный диапазон (лучше всего использовать массив) вашего значения.
2. И после этого вам нужно ввести эту формулу, используя Ctrl+Shift+Enter как правильную формулу массива.

Итак, теперь ваша формула будет такой:

Несколько критериев с разными столбцами

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

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

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

 =SUM(SUMIFS(D2:D21,B2:B21,{"Damage","Faulty"},C2:C21,"No"))

Заключение

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

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

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