Общая формула
=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!"&rng),criteria))
Резюме
Чтобы подсчитать совпадения во всей книге, вы можете использовать формулу, основанную на функциях СЧЁТЕСЛИ и СУММПРОИЗВ. В показанном примере формула в D5:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!A1:Z10000"),B5))
где «листов» - именованный диапазон B8: B10.
Контекст
В этой книге четыре листа. Три рабочих листа, «Лист1», «Лист2» и «Лист3» содержат 1000 случайных имен в таблице, которая выглядит следующим образом:
Примечание: диапазон, который мы используем в формуле, A1: Z10000, произвольный и должен быть скорректирован в соответствии с вашими данными.
Объяснение
Работая изнутри, у нас сначала есть это выражение внутри функции ДВССЫЛ:
"'"&sheets&"'!A1:Z10000"
Поскольку «листы» - это именованный диапазон, содержащий «Sheet1», «Sheet2» и «Sheet3», мы получаем такой массив после вычисления выражения:
("'Sheet1'!A1:Z10000";"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000")
Внутри массива у нас есть три значения, каждое из которых представляет собой имя листа, присоединенное путем конкатенации к диапазону A1: Z10000. Обратите внимание, что это все текстовые значения.
Затем функция ДВССЫЛ используется для преобразования каждого текстового значения в правильную ссылку, которая передается функции СЧЁТЕСЛИ в качестве аргумента диапазона вместе со значением в D5 для критериев.
Поскольку мы дали COUNTIF три отдельных диапазона, мы возвращаем три результата в виде такого массива:
(5;6;5)
Каждый предмет считается одним листом.
Наконец, СУММПРОИЗВ используется для суммирования массива и возвращает результат 16.
