I'm trying to get the average depending on another cell.
Basically I want the average of all cells where the value of the upper right cell equals 95.

In this example I would like the average of A3, A6, A7, A8, A9 .

enter image description here

  • What about A1? There can't be any cells offset up to the right from it. Nov 2, 2014 at 1:31

2 Answers 2


Your question is tagged with Excel-2013 so I would suggest the AVERAGEIF function offsetting the optional average_range by one row compared to the range.


     AVERAGEIF with offset

While the size of the average_range and average_range have to stay the same, there is nothing stopping you from offsetting one from the other by rows, columns or both.


Try the functions sumif and countif. In your case:

=SUMIF($B$1:$B$9, "=95", $A$1:$A$9)/COUNTIF($B$1:$B$9, "=95")

If you put 95 in a cell say C1, then

=SUMIF($B$1:$B$9, "=" & $C$1, $A$1:$A$9)/COUNTIF($B$1:$B$9, "=" & $C$1)

Of course you can improve upon this (handle when nothing matches) but this is the main idea.

  • Shouldn't the sum_range used in your SUMIF be offset one row from the range used for the criteria? e.g. SUMIF($B$1:$B$9, 95, $A$2:$A$10)
    – user385793
    Nov 2, 2014 at 12:54
  • @Jeeped true but if he really wanted that, he can adjust the formula. Behavior of edge cases should be dealt with by the original poster.
    – Peon
    Nov 2, 2014 at 22:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .