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)
You're nearly right. You can adapt your formula slightly like this:
{=SUM(IF(A1:A5=B1:B5,1,0))}
Type this in without the curly {} brackets, and press Ctrl+Shift+Enter so that Excel knows it's an array formula. The curly brackets represent the fact it's an array formula.
Best Answer
I answer myself as I do this at least once a year and I feel the need of having a place to look for quickly when I forget something, plus I think this could be useful for someone else too.
So, ok, I know of two ways to do that:
using conventional formula
in
D1put=COUNTIF(A1:B10,"*"&C1&"*")Some notes (mostly) for newbies:
those "*" in the formula are Excel wildcards (a list of Excel functions that can use wildcards)
those "&" are a way to concatenate text without using CONCATENATE (more on this here or here too )
using CSE/array formula
D1should be{=SUM(IF(ISNUMBER(SEARCH(C1,A1:B10)),1,0))}(more about Excel array formula)