Загальна формула
=SUMPRODUCT(--(logical_expression))
Резюме
Для підрахунку рядків у таблиці, які відповідають внутрішнім, обчисленим критеріям, без використання допоміжного стовпця, ви можете використовувати функцію SUMPRODUCT.
Контекст
Уявіть, у вас є таблиця показників продажів кількох товарів. У вас є стовпець продажів минулого місяця та стовпець продажів поточного місяця. Ви хочете підрахувати товари (рядки), де поточні продажі менші, ніж продажі минулого місяця. Ви не можете використовувати для цього COUNTIF, оскільки COUNTIFs - це функція на основі діапазону. Один із варіантів - додати допоміжний стовпець, який віднімає продажі минулого місяця від продажів цього місяця, а потім використовуйте COUNTIF для підрахунку результатів менше нуля. Але що, якщо ви не хочете (або не можете) додати допоміжний стовпець? У цьому випадку ви можете використовувати SUMPRODUCT.
У наведеному прикладі формула в комірці G6 має вигляд:
=SUMPRODUCT(--(C5:C10>D5:D10))
Пояснення
SUMPRODUCT призначений для роботи з масивами. Він помножує відповідні елементи на два або більше масиви і підсумовує отримані добутки. Як результат, ви можете використовувати SUMPRODUCT для обробки масивів, які є результатом критеріїв, застосованих до діапазону комірок. Результатом таких операцій стануть масиви, з якими SUMPRODUCT може обробляти спочатку, не вимагаючи синтаксису Control Shift Enter.
У цьому випадку ми просто порівнюємо значення у стовпці C зі значеннями у стовпці D, використовуючи логічний вираз:
C5:C10>D5:D10
Оскільки ми маємо справу з діапазонами (масивами), результатом є масив TRUE FALSE значень, як це:
(FALSE; TRUE; FALSE; TRUE; FALSE; FALSE)
Щоб примусити їх до одиниць і нулів, ми використовуємо подвійний негативний оператор (також званий подвійним унарним):
--(C5:C10>D5:D10)
Що створює та масив, подібний до цього:
(0; 1; 0; 1; 0; 0)
який потім обробляється SUMPRODUCT. Оскільки масив лише один, SUMPRODUCT просто додає елементи масиву і повертає загальну суму.
