Загальна формула
=SUMPRODUCT(--(range1=criteria),range2)
Резюме
Для підсумовування або проміжних підсумків на основі критеріїв у сусідніх стовпцях можна використовувати формулу на основі функції SUMPRODUCT. У наведеному прикладі формула в J5 має вигляд:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Пояснення
По суті, ця формула використовує SUMPRODUCT для множення, а потім суми добутків двох масивів: array1 і array2 . Перший масив array1 налаштований на функцію "фільтра", щоб дозволити лише значення, що відповідають критеріям.
Array1 використовує діапазон, який починається з першого стовпця, що містить значення, які повинні передавати критерії. Ці "значення критеріїв" розміщуються у стовпці ліворуч і безпосередньо поруч із "значеннями даних".
Критерії застосовуються як простий тест, який створює масив значень TRUE і FALSE:
--($B5:$H5=J$4)
Цей біт формули "перевіряє" кожне значення в першому масиві, використовуючи надані критерії, а потім використовує подвійне від'ємне значення (-), щоб примусити отримані значення TRUE і FALSE до 1 і 0. Результат виглядає так:
(1,0,0,0,1,0,1)
Зауважте, що 1 відповідають стовпцям 1,5 та 7, які відповідають критеріям "А".
Для масиву2 всередині SUMPRODUCT ми використовуємо діапазон, який "зсувається" на один стовпець праворуч. Цей діапазон починається з першого стовпця, що містить значення для підсумовування, і закінчується останнім стовпцем, що містить значення для підсумовування.
Отже, у прикладі формули в J5, після заповнення масивів ми маємо:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Оскільки SUMPRODUCT запрограмований спеціально на ігнорування помилок, що виникають в результаті множення текстових значень, остаточний масив виглядає так:
(1,0,0,0,1,0,1)
Єдиними значеннями, які "переживають" множення, є ті, що відповідають 1s усередині масиву1 . Ви можете думати про логіку в array1, "фільтруючи" значення в array2 .
