# Finding average in a single range with multiple criteria

#### bwatkiss

##### New Member
Hello, i’m trying to do a formula that gives me the average of scores from one column based on the group the individuals are in in another column. For example, let’s say I have 20 groups with four or five people in each of those groups designated in their column by their group number. In another column each of those individuals has a score, how would I then add up each of those scores for those in the same group and get the average for that group put into another column using a formula?

thank you!

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Have a look at the AverageIf function.

#### bwatkiss

##### New Member
I have without success

when using this I get too many arguments error.

=AVERAGEIF(J16:J97,”=1”, J16:J97,”=2”, L16:L97)

for context the J16:J97 is the range with the group numbers, 1 and 2 are the groups, there are 20 groups but I’m just trying to get it to work on two groups first. L16:L97 is the range with th individual scores to average.

#### Fluff

##### MrExcel MVP, Moderator
I thought you were trying to get the average per group, not multiple groups.

#### bwatkiss

##### New Member

Yes I am trying to get the average score per group and each group has 4 or 5 scores in it. So basically I need something that calculates the mean for 20 different groups and each group has four or five members.

#### Fluff

##### MrExcel MVP, Moderator
In that case try
Excel Formula:
``=AVERAGEIF(J16:J97,1, L16:L97)``
where col J is the group & col L is the scores

#### bwatkiss

##### New Member

Yes I’ve use that and got it to work on a single group. I need a formula that does it across 20 groups.

I have 82 individuals in my list, each with their own score. With four or five individuals being in each group, 1 through 20. So I need a formula that recognizes they are in the same group and then takes that individual score adds it to each of the other member’s scores of that group and then gives the average for that group. Therefore I would have potentially 20 different averages.

#### Fluff

##### MrExcel MVP, Moderator
You need to repeat the formula in other cells for each group.

#### bwatkiss

##### New Member
The order of the list will change with each iteration and so that won’t work, one week they may be in group one the next week they may be in group 5.

#### Fluff

##### MrExcel MVP, Moderator
Sorry, but I don't understand what you are saying.
You originally asked how to get the average based on the group, which is what the formula does. Is that not what you actually want?

Replies
7
Views
185
Replies
4
Views
190
Replies
3
Views
215
Replies
3
Views
387
Replies
21
Views
295

1,140,917
Messages
5,703,168
Members
421,279
Latest member
emzy

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