AVERAGEIFS based on dynamic partial text match

[kingspur06]

Hi - I am trying to figure out if there is a way to trigger a partial text lookup against another cell within the AVERAGEIFS formula.
In the below example, I can get an average just by creating a static formula that shows the text I am searching for.
But I want to be able to enter a value in Cell E6 and have the formula do a search for anything that contains that particular text.
I don't know how to use wildcards against a cell reference.

Also, it would be great if there was a way to make the AVERAGEIFS function ignore any values that are 0, so in the Apple example, instead of giving the answer as 5, it would show as 6.

Any suggestions greatly welcomed!
many thanks
Mark

Book1
	A	B	C	D	E
1	APPLE 1	7
2	APPLE 2	4		Static Formula	5
3	APPLE 3	6
4	APPLE 4	8
5	APPLE 5	5
6	APPLE 6	0		Selection	Apple
7	BANANA 1	7
8	BANANA 2	4
9	BANANA 3	5
10	BANANA 4	8
11	BANANA 5	2
12	BANANA 6	0
Sheet1

Cell Formulas
Range	Formula
E2	E2	=AVERAGEIFS(B:B,A:A,"*apple*")

 

[jasonb75]

It should be

=AVERAGEIFS(B:B,B:B,">0",A:A,"*"&E6&"*")

 

[kingspur06]

Fantastic work - many thanks!