# calculate the average insde an array

#### Greenstuffs

##### New Member
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>

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Excel Workbook
ABCDEFG
1EuropeEuropeAsiaAsiaAmericaAmerica
2Europe125955
3Europe145345
4Asia165935
5Asia685925
6America1105315
7America1125505
8
93.75
Sheet1

Maybe...

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

wow that was quick
Thank You very much

@Robert -- much better.

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

Could you specify?
if you mean like that:
Excel Workbook
ABCDEFG
1*EuropeEuropeAsiaAsiaAmericaAmerica
2Europe125955
3Europe145345
4Asia1659A5
5Asia685925
6America1105315
7America1125505
8*******
94******
103******
Sheet1

It is actually other way around.

Replies
1
Views
179
Replies
4
Views
208
Replies
6
Views
187
Replies
5
Views
221
Replies
5
Views
182

1,207,283
Messages
6,077,518
Members
446,287
Latest member
tjverdugo85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back