У меня проблемы с использованием SUMPRODUCT вместе с фильтром. У меня есть следующая формула:
=SUMPRODUCT(LEN(B3:B7)-LEN(SUBSTITUTE(B3:B7;".";"")))
Мне нужно посчитать, сколько "." находятся в диапазоне. Однако, когда я использую некоторые фильтры, он всегда учитывает все "." в этом диапазоне, но я хочу, чтобы он считал только видимые.
Нормальный:
После фильтра:
Как я могу это сделать?
Добавьте условие в формулу SUMPRODUCT :
=SUMPRODUCT((LEN(D3:D8)-LEN(SUBSTITUTE(D3:D8,".","")))*SUBTOTAL(103,OFFSET(D$2,ROW($A$1:$A$6),0)))
SUBTOTAL может игнорировать скрытые строки. В этом случае мы используем 103 потому что он возвращает 1 для непустых ячеек, что полезно. Мы должны изменить его с помощью функции OFFSET чтобы он возвращал счет в виде массива вместо одного значения. Ключевым моментом является выбор диапазонов.
D3:D8 должен быть диапазоном, в котором вы хотите посчитать текст.
D$2 должна быть ячейка прямо выше этого диапазона
$A$1:$A$6 может быть в любом столбце, но он должен начинаться со строки 1 и иметь столько строк, сколько диапазон, в котором вы считаете текст. Я использовал A1:A6 просто по привычке и удобству. Он служит только для того, чтобы дать нам массив чисел для подачи в OFFSET поэтому он может заставить SUBTOTAL хорошо играть с SUMPRODUCT .
