Подсчёт строк по нескольким критериям в Excel

Чтобы подсчитать количество строк в диапазоне по нескольким критериям, часть из которых определяется логическими проверками на уровне отдельных строк, используйте функцию СУММПРОИЗВ (SUMPRODUCT) в Excel.

Например, у меня есть отчёт о товарах с плановыми и фактическими продажами, и теперь я хочу подсчитать строки с товаром «Яблоко», у которого фактические продажи превышают плановые, как показано на скриншоте ниже. Для решения этой задачи наиболее эффективной будет функция СУММПРОИЗВ (SUMPRODUCT).

Подсчёт строк по нескольким критериям с помощью функции СУММПРОИЗВ (SUMPRODUCT)

Подсчёт строк по нескольким критериям с помощью функции СУММПРОИЗВ (SUMPRODUCT)

Чтобы подсчитать строки по нескольким критериям с использованием функции СУММПРОИЗВ (SUMPRODUCT) в Excel, применяйте общий синтаксис:

• logical1, logical2Логические выражения, предназначенные для сравнения значений.

1. Чтобы подсчитать количество строк с товаром «Яблоко», где фактические продажи превышают плановые, используйте приведённую ниже формулу:

Примечание: в приведённой выше формуле C2:C10>B2:B10 — это первое логическое выражение, сравнивающее значения в столбце C со значениями в столбце B; A2:A10=E2 — второе логическое выражение, проверяющее, содержится ли значение ячейки E2 в столбце A.

2. Затем нажмите клавишу Enter, чтобы получить нужный результат (см. скриншот):

Пояснение формулы:

=SUMPRODUCT(($C$2:$C$10>,$B$2:$B$10)*($A$2:$A$10=E2))

• $C$2:$C$10>$B$2:$B$10: это логическое выражение построчно сравнивает значения в столбце C со значениями в столбце B. Если значение в столбце C больше соответствующего значения в столбце B, результат — ИСТИНА (TRUE), в противном случае — ЛОЖЬ (FALSE). В итоге формируется массив следующего вида: {ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА}.
• $A$2:$A$10=E2: это логическое выражение проверяет, содержится ли значение ячейки E2 в диапазоне A2:A10. В результате вы получите массив вида: {ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ}.
• ($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2): операция умножения объединяет эти два массива в один и возвращает результат следующего вида: {1;0;1;0;0;0;0;1;0}.
• SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))= SUMPRODUCT({1;0;1;0;0;0;0;1;0}): эта функция СУММПРОИЗВ (SUMPRODUCT) суммирует элементы массива и возвращает результат — 3.

Используемая относительная функция:

Другие статьи: