Excel-Count number of visible cells containing certain string

I have a sheet in which I first apply a filter to a certain column to show "Unique records only". Now I have a column which contains, for e.g., values like:

Applepie
Applesauce
Crabapple
Banana
Mango

What I need is the count of all visible cells containing the word "Apple". Note that this should exclude the hidden (filtered out) cells.
Now I have found out a formula:

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

However the problem with this one is this formula searches for the entire text. NOT just a part of it. How do I modify this formula to return count of cells containing "Apple" i.e. for this example it should return 3 as the result.

COUNTIF with wildcards will work alone to count all rows with "Apple" somewhere in the text......but it isn't restricted to visible rows and you can't use COUNTIF in conjunction with your SUBTOTAL formula.

Adjust your formula like this using SEARCH function

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

Note: that version may be slow, referencing the whole column - better to restrict the ranges if you can

Alternative approach

If you want to avoid that long formula then you could use a helper column to indicate whether each row is filtered or not, e.g. in Sheet1 Z2 use this formula copied down

=SUBTOTAL(3,A2)

Now you can use a COUNTIFS function to count visible rows containing "Apple"

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