# Calculate averages from several data ranges

Hello,

I have the following problem: a table contains names of pupils in the first two columns (A and B). The next columns are:

- a code for the type of grade (e.g. M for oral exam, T for test, etc.)
- the integer part of the grade (as a number,
**not**as A, B, an so on) - the decimal part of the grade (believe it or not, in some countries they actually do that)
- the percentage, which is kind of irrelevant in this question.

So basically there are grades in four-column-ranges or blocks, starting from column 3 (C) and going to the end of the long table.

What I would like to do is calculate the average grades for a certain type (like the average of all Ms). How do I look up what is in the first grade-column and decide if I want to add the second and third columns to the total, depending on whether that's an M or not? And, obviously, I would need to count the Ms too, but that's probably easier.

(Edited to add this example)

(Second edit: It may very well be that this is not the best way to organize this type of data for this problem, in which case I am open also to suggestions on how to improve the design itself)

Please attach a sample file with the needed comments,to know how the data are organizer and what it's expected to be achieved.

There is the

`AVERAGEIF()`

function which takes the average from one dataset (e.g. the grades column) for entries where the corresponding item from another dataset (assessment type column) satisfies a given condition. If you also need the count of matches, it may be just as easy to use`SUMIF()`

and`COUNTIF()`

functions.For summary with multiple conditions there are also the

`AVERAGEIFS()`

,`SUMIFS()`

and`COUNTIFS()`

functions (trailing "S" for "Set of conditions").If you need to create a weighted average, the

`SUMPRODUCT()`

function is also useful.I have uploaded a sample file.

@keme That is very useful indeed. Now I would like to know how to specify the range in order to get the

`AVERAGEIF()`

function (or any other) to look only in the first column of each 4-column range for the condition and get the numbers from the second column of the 4-column range (the fact that I would need the third column for the decimal part I can adjust myself accordingly).