Суммирование с критерием «ИЛИ» в Excel

Суммирование с критерием «ИЛИ» в Excel может оказаться несколько сложнее, чем вы ожидаете. В этой статье вы найдёте несколько простых, но полезных примеров.

1. Начнем с простого. Допустим, необходимо просуммировать ячейки, которые отвечают следующим критериям: содержат слова «Google» или «Facebook» (один столбец с критериями). С этим легко справится следующая формула:

   =SUMIF(A1:A8,"Google",B1:B8)+SUMIF(A1:A8,"Facebook",B1:B8)
=СУММЕСЛИ(A1:A8;"Google";B1:B8)+СУММЕСЛИ(A1:A8;"Facebook";B1:B8)

   

2. Если же у нас будет 2 столбца с критериями, и мы захотим также просуммировать ячейки в строках, которые содержат «Google» или «Stanford» (два диапазона критериев), то не можем просто использовать функцию SUMIF (СУММЕСЛИ) дважды (см. рисунок ниже), как в предыдущем примере. Ячейки со значениями в строках 1 и 7 просуммированы два раза, хотя должны подсчитываться только один раз. 10 – вот правильный результат!

   =SUMIF(A1:A8,"Google",C1:C8)+SUMIF(B1:B8,"Facebook",C1:C8)
=СУММЕСЛИ(A1:A8;"Google";C1:C8)+СУММЕСЛИ(B1:B8;"Facebook";C1:C8)

   

3. Нам нужна формула массива. Используем функцию IF (ЕСЛИ), чтобы проверить, попадаются ли критерии «Google» или «Stanford» в строке:

   =IF((A1="Google")+(B1="Stanford"),1,0)*C1
=ЕСЛИ((A1="Google")+(B1="Stanford");1;0)*C1

   

   Пояснение:

   • ИСТИНА = 1, ЛОЖЬ = 0.
   • Для первой строки, функция IF (ЕСЛИ) возвращает следующее значение:

     =IF(TRUE+TRUE,1,0)*3 ► =IF(2,1,0)*3 ► 3
=ЕСЛИ(ИСТИНА+ИСТИНА;1;0)*3 ► =ЕСЛИ(2;1;0)*3 ► 3

     Таким образом, значение 3 будет учитываться.

   • Для второй строки:

     =IF(FALSE+FALSE,1,0)*5 ► =IF(0,1,0)*5 ► 0
=ЕСЛИ(ЛОЖЬ+ЛОЖЬ;1;0)*5 ► =ЕСЛИ(0;1;0)*5 ► 0

     Таким образом, значение 5 учитываться не будет.

   • А для третьей:

     =IF(FALSE+TRUE,1,0)*2 ► =IF(1,1,0)*2 ► 2
=ЕСЛИ(ЛОЖЬ+ИСТИНА;1;0)*2 ► =ЕСЛИ(1;1;0)*2 ► 2

     То есть, значение 2 учитываться будет, и т.д.

4. Теперь всё, чего нам не хватает, это функции SUM (СУММ), которая суммирует эти значения. Давайте добавим функцию SUM (СУММ) и заменим А1 на A1:A8, B1 на B1:B8 и C1 на С1:C8.

   =SUM(IF((A1:A8="Google")+(B1:B8="Stanford"),1,0)*C1:C8)
=СУММ(ЕСЛИ((A1:A8="Google")+(B1:B8="Stanford");1;0)*C1:C8)

   

5. Закончим, нажав Ctrl+Shift+Enter.

   =SUM(IF((A1:A8="Google")+(B1:B8="Stanford"),1,0)*C1:C8)
=СУММ(ЕСЛИ((A1:A8="Google")+(B1:B8="Stanford");1;0)*C1:C8)

   

Примечание: Строка формул указывает, что это формула массива, заключая её в фигурные скобки {}. Их не нужно вводить самостоятельно. Они исчезнут, когда вы начнете редактировать формулу.

5. Пояснение:
   • Диапазон (массив констант), созданный с помощью функции IF (ЕСЛИ), хранится в памяти Excel, а не в ячейках листа.
   • Массив констант выглядит следующим образом: {1;0;1;0;1;0;1;0}.
   • Он умножается на C1:C8 и это дает {3;0;2;0;4;0;1;0}.
   • Последний массив констант используется в качестве аргумента для функции SUM (СУММ), давая результат 10.
6. Мы можем сделать ещё один шаг вперёд. К примеру, подсчитать количество строк, которые содержат «Google» и «Stanford», либо «Columbia».

   =SUM(IF((A1:A8="Google")*(B1:B8="Stanford")+(B1:B8="Columbia"),1,0)*C1:C8)
=СУММ(ЕСЛИ((A1:A8="Google")*(B1:B8="Stanford")+(B1:B8="Columbia");1;0)*C1:C8)