Excel-Count количество видимых ячеек, содержащих определенную строку

Question

У меня есть лист, в котором я сначала применяю фильтр к определенному столбцу, чтобы показать "Только уникальные записи". Теперь у меня есть столбец, который содержит, например, такие значения, как:

Applepie
Applesauce
Crabapple
Banana
Mango

Что мне нужно, так это счетчик всех видимых ячеек, содержащих слово "Apple". Обратите внимание, что это должно исключать скрытые (отфильтрованные) ячейки.
Теперь я нашел формулу:

`=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1))*(Sheet1!A:A="<Text to search>"))`

Однако проблема с этим в том, что эта формула ищет весь текст. НЕ просто часть этого. Как мне изменить эту формулу, чтобы она возвращала количество ячеек, содержащих "Apple", то есть для этого примера она должна вернуть 3 в качестве результата.

Answer 1

COUNTIF с подстановочными знаками будет работать в одиночку для подсчета всех строк с "Apple" где-то в тексте ...... но он не ограничен видимыми строками, и вы не можете использовать COUNTIF в сочетании с формулой SUBTOTAL .

Настройте формулу, как это, используя функцию SEARCH

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!A:A,ROW(Sheet1!A:A)-MIN(ROW(Sheet1!A:A)),,1)),ISNUMBER(SEARCH("Apple",Sheet1!A:A))+0)

Примечание: эта версия может быть медленной, ссылаясь на весь столбец - лучше ограничить диапазоны, если вы можете

Альтернативный подход

Если вы хотите избежать этой длинной формулы, вы можете использовать вспомогательный столбец, чтобы указать, фильтруется ли каждая строка или нет, например, в Sheet1 Z2 используйте эту формулу, скопированную вниз.

=SUBTOTAL(3,A2)

Теперь вы можете использовать функцию COUNTIFS для подсчета видимых строк, содержащих "Apple"

=COUNTIFS(A:A,"*Apple*",Z:Z,1)

Answer 2

Тест: =COUNTIF(A2:A10;"Apple*") range изменения