Общая формула
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Резюме
Чтобы условно суммировать идентичные диапазоны, которые существуют на отдельных листах, все в одной формуле, вы можете использовать функцию СУММЕСЛИ с ДВССЫЛ, заключенную в СУММПРОИЗВ. В показанном примере формула C9 имеет следующий вид:
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))
Объяснение
Данные на каждом из трех обрабатываемых листов выглядят так:
Прежде всего, обратите внимание, что вы не можете использовать СУММЕСЛИ с «нормальной» трехмерной ссылкой следующим образом:
Sheet1:Sheet3!D4:D5
Это стандартный «3D-синтаксис», но если вы попытаетесь использовать его с SUMIF, вы получите ошибку #VALUE. Итак, чтобы обойти эту проблему, вы можете использовать именованный диапазон «листов», в котором перечислены все листы (вкладка рабочего листа), которые вы хотите включить. Однако для создания ссылок, которые Excel будет правильно интерпретировать, нам нужно объединить имена листов с диапазонами, с которыми нам нужно работать, а затем использовать КОСВЕННЫЙ, чтобы Excel распознал их правильно.
Кроме того, поскольку именованный диапазон "листов" содержит несколько значений (то есть его массив), результатом СУММЕСЛИ в этом случае также является массив (иногда называемый "результирующим массивом). Итак, мы используем СУММПРОИЗВ для его обработки, поскольку СУММПРОИЗВ имеет возможность обрабатывать массивы изначально, не требуя Ctrl-Shift-Enter, как и многие другие формулы массива.
Другой путь
Приведенный выше пример несколько сложен. Другой способ решить эту проблему - выполнить «локальную» условную сумму на каждом листе, а затем использовать обычную трехмерную сумму для суммирования каждого значения на вкладке сводки.
Для этого добавьте формулу СУММЕСЛИ на каждый лист, который использует ячейку критериев на сводном листе. Затем, когда вы измените критерии, все связанные формулы СУММЕСЛИ обновятся.
