combine SUMPRODUCT & COUNTIFS

Hi Experts

I have following problem/issue:
I want a formula to count (to return) the number of occurrences of a (sub)string in a filtered column
where the count takes into account only the visible cells in the selection

I think I might need a combination of 2 formula's (SUMPRODUCT & COUNTIFS)
1. =SUMPRODUCT(SUBTOTAL(3;OFFSET(G4:G99999;ROW(G4:G99999)-ROW(G4);0;1));--(G4:G99999="ART"))
2. =COUNTIFS(G4:G99999;"*OR*")

Formula 1. does the job, but can only be used for complete strings, not parts of strings
Formula 2. counts the correct occurrences of a (sub)string, but counts the whole range of the column, not just the visible cells of the filtered column

Example:
COLUMN A is filled with data an has an AutoFilter on it in Cell G3
I want the formula to give it's result in cell A1
Formula 1.
=SUMPRODUCT(SUBTOTAL(3;OFFSET(G4:G99999;ROW(G4:G99999)-ROW(G4);0;1));--(G4:G99999="ART"))
does the job, but can only be used for complete occurrences of the search strings, not parts...

Formula 2. =COUNTIFS(G4:G99999;"*OR*")
counts the correct occurrences of a (sub)string, but counts the whole range of the column, not just the visible cells of the filtered column

I definitely need help on this one.
Anyone can help me out on this?
Thx in advance for sharing your wisdom ;-)

Greets
A

Thank you very much; that did the trick!!!

To make things easier i refer to cell H1 where I enter the search string; so that gives:
=SUMPRODUCT(SUBTOTAL(3;OFFSET(H4:H99999;ROW(H4:H99999)-ROW(H4);0;1));--ISNUMBER(SEARCH(CONCATENATE("*";H1;"*");H4:H99999)))

Once again, Thx!
[Issue Solved]