calculate the average insde an array

[Greenstuffs]

I have the following data and i need to get the average lets say between asia and america, i can't consolidate the names because they are actually different countries in each continent but i need to know like the average of the countries between the continents, and to make things even harder sometimes i get 0s in the data. I have tried index and match functions but can only pick up one, sumproduct doesn't work all the way. Any input is appreciated
Thx

	Europe	Europe	Asia	Asia	America	America
Europe	1	2	5	9	5	5
Europe	1	4	5	3	4	5
Asia	1	6	5	9	3	5
Asia	6	8	5	9	2	5
America	1	10	5	3	1	5
America	1	12	5	5	0	5

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Robert Mika]

Excel Workbook
	A	B	C	D	E	F	G
1		Europe	Europe	Asia	Asia	America	America
2	Europe	1	2	5	9	5	5
3	Europe	1	4	5	3	4	5
4	Asia	1	6	5	9	3	5
5	Asia	6	8	5	9	2	5
6	America	1	10	5	3	1	5
7	America	1	12	5	5	0	5
8
9	3.75
Sheet1

 

[shg]

Maybe...

=SUMPRODUCT((B1:G1="America")*(A2:A7="Asia"), B2:G7) / (COUNTIF(B1:G1, "America")*COUNTIF(A2:A7,"Asia"))

 

[Greenstuffs]

wow that was quick
Thank You very much

 

[shg]

@Robert -- much better.

 

[Greenstuffs]

Actually i tried both formulas and both worked, Robert ones would work as long as there are no alphabetic characters in the array and shg would work even though there are characters in the array. Both are good.
Again thank you for sharing your knowledge

 

[Robert Mika]

Could you specify?
if you mean like that:

Excel Workbook
	A	B	C	D	E	F	G
1	*	Europe	Europe	Asia	Asia	America	America
2	Europe	1	2	5	9	5	5
3	Europe	1	4	5	3	4	5
4	Asia	1	6	5	9	A	5
5	Asia	6	8	5	9	2	5
6	America	1	10	5	3	1	5
7	America	1	12	5	5	0	5
8	*	*	*	*	*	*	*
9	4	*	*	*	*	*	*
10	3	*	*	*	*	*	*
Sheet1

It is actually other way around.