Загальна формула
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Резюме
Щоб умовно підсумувати однакові діапазони, які існують на окремих робочих аркушах, все в одній формулі, ви можете використовувати функцію SUMIF із INDIRECT, загорнуту в SUMPRODUCT. У наведеному прикладі формула в С9 така:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Пояснення
Дані на кожному з трьох аркушів, що обробляються, виглядають так:
Перш за все, зверніть увагу, що ви не можете використовувати SUMIF з "звичайним" 3D-посиланням, як це:
Sheet1:Sheet3!D4:D5
Це стандартний "3D-синтаксис", але якщо ви спробуєте використовувати його з SUMIF, ви отримаєте помилку #VALUE. Отже, для вирішення цієї проблеми ви можете використовувати названий діапазон "аркушів", у якому перелічується кожен аркуш (вкладка робочого аркуша), який потрібно включити. Однак, щоб побудувати посилання, які Excel буде правильно інтерпретувати, нам потрібно об’єднати імена аркушів у діапазони, з якими нам потрібно працювати, а потім використати INDIRECT, щоб Excel розпізнав їх правильно.
Крім того, оскільки названий діапазон "аркушів" містить кілька значень (тобто його масив), результатом SUMIF у цьому випадку є також масив (який іноді називають "результуючим масивом). Отже, ми використовуємо SUMPRODUCT для обробки, оскільки SUMPRODUCT має можливість обробляти масиви спочатку, не вимагаючи Ctrl-Shift-Enter, як і багато інших формул масиву.
Інший спосіб
Наведений вище приклад дещо складний. Інший спосіб вирішити цю проблему - скласти "локальну" умовну суму на кожному аркуші, а потім використовувати звичайну тривимірну суму, щоб скласти кожне значення на вкладці "Підсумок".
Для цього додайте формулу SUMIF до кожного аркуша аркуша, який використовує комірку критеріїв на аркуші зведення. Тоді при зміні критеріїв усі пов’язані формули SUMIF оновляться.
