Общая формула
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Резюме
Чтобы подсчитать видимые строки только с критериями, вы можете использовать довольно сложную формулу, основанную на СУММПРОИЗВ, ПРОМЕЖУТОЧНЫЙ ИТОГ и СМЕЩЕНИЕ. В показанном примере формула в C12:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Предисловие
Функция ПРОМЕЖУТОЧНЫЙ ИТОГ может легко генерировать суммы и подсчеты для скрытых и не скрытых строк. Однако он не может обрабатывать такие критерии, как СЧЁТЕСЛИ или СУММЕСЛИ, без какой-либо помощи. Одно из решений - использовать СУММПРОИЗВ для применения функции ПРОМЕЖУТОЧНЫЙ ИТОГ (через СМЕЩЕНИЕ) и критериев. Подробности этого подхода описаны ниже.
Объяснение
По сути, эта формула работает путем создания двух массивов внутри SUMPRODUCT. Первый массив применяет критерии, а второй массив обрабатывает видимость:
=SUMPRODUCT(criteria*visibility)
Критерий применяется с частью формулы:
=(C5:C8=C10)
Что генерирует такой массив:
(FALSE;TRUE;FALSE;TRUE)
Где ИСТИНА означает «соответствует критериям». Обратите внимание, поскольку мы используем умножение (*) в этом массиве, значения TRUE FALSE будут автоматически преобразованы в 1 и 0 с помощью математической операции, поэтому мы получим:
(0;1;0;1)
Фильтр видимости применяется с помощью SUBTOTAL с номером функции 103.
SUBTOTAL может исключать скрытые строки при выполнении вычислений, поэтому мы можем использовать его в этом случае для создания «фильтра» для исключения скрытых строк внутри SUMPRODUCT. Проблема в том, что SUBTOTAL возвращает одно число, а нам нужен массив результатов, чтобы успешно использовать его внутри SUMPRODUCT. Уловка состоит в том, чтобы использовать OFFSET для подачи SUBTOTAL по одной ссылке на строку, так что OFFSET будет возвращать один результат для каждой строки.
Конечно, для этого нужен другой трюк, который состоит в том, чтобы дать OFFSET массив, содержащий одно число в строке, начиная с нуля. Мы делаем это с помощью выражения, построенного на функции СТРОКА:
=ROW(C5:C8)-MIN(ROW(C5:C8)
который сгенерирует такой массив:
(0;1;2;3)
Таким образом, второй массив (который обрабатывает видимость с помощью SUBTOTAL) создается следующим образом:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
И, наконец, имеем:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Что возвращает 1.
Несколько критериев
Вы можете расширить формулу для обработки нескольких критериев, например:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Подведение итогов
Чтобы вернуть сумму значений вместо количества, вы можете адаптировать формулу, включив в нее диапазон сумм:
=SUMPRODUCT(criteria*visibility*sumrange)
Массивы критериев и видимости работают так же, как описано выше, за исключением невидимых ячеек. Если вам нужно частичное сопоставление, вы можете построить выражение, используя ISNUMBER + SEARCH, как описано здесь.
