Average of a filter of multiple columns in Excel

2
votes

I would like a formula to display the average of a number of filtered values in Excel. For example, in column A I have the year, in column B I have the month, and in column C I have the values.

So I would like to select the 2011 year, the month of May and get the average of the values in column C that satisfy these requirements. I'm thinking a function like...

=AVERAGE(IF(AND($A$1:$A:$100=2011,$B$1:$B:$100=May),$C$1:$C:$100))

But I don't think this will work, and I've tried it.

2
excelfilteraverage

2 Answers

3
votes

Use the AverageIfs function. e.g.

AVERAGEIFS(C1:C100,A1:A100,"=2011",B1:B100,"=May")

0
votes

You can use SUMIF or SUMPRODUCT:

=AVERAGE(SUMIF($C$1:$C:$100,$A$1:$A:$100,2011,$B$1:$B:$100,"May"))

Edit: I think the solution by atov is better. Meh. Still works :)