COUNTIFS with wildcard and or function

[Cat129]

I'm stuggling with this formula. I need to do a COUNTIF that uses wildcards and also an or function.

The below is what I started with but then I realised that any cell that contained the exact contents of A8 isn't picked up in the count.

=COUNTIF('Sheet 1'!$Q:$Q,"*"&A8&"*")

Say for example cell A8 Contained "452"

Can anyone help me work out a COUNTIF that looks for "452" and "452, 479" or "124, 452". To try to explain further if the three below examples were in my range the COUNTIF would total 3

[TABLE="width: 500"]
<tbody>[TR]
[TD]452
[/TD]
[/TR]
[TR]
[TD]452, 479[/TD]
[/TR]
[TR]
[TD]124, 452[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

=sumproduct((isnumber(search(","&a8&",",","&substitute(Sheet1!$q$2:$q$400," ","")&","))+0))