How to get the average of a column data where another column contains a value in a specific range

Question

Lets say I have two columns in a sheet "Data".

• Column A is the Animal
• Column B is the Time value hh:mm:ss

I would like to get the average Time value of all Animals in Column A that are in a specific range of another sheet "Animals" range J50:J90. In this example the average should not include Tree as "Tree" is not in the specific range (J50:J90).

The sheet for the below is called "Data"

   |    A     |    B     |
___|__________|__________|
1  |   Animal |   Time   |
___|__________|__________|
2  |   dog    | 00:30:10 |
___|__________|__________|
3  |   cat    | 02:11:00 |
___|__________|__________|
4  |   tree   | 00:45:00 |
___|__________|__________|

The sheet containing the range is "Animals", Column J, rows 50 to 90

   |    J     |
___|__________|
50 |   dog    | 
___|__________|
51 |   cat    |
___|__________|
52 |   bird   |
___|__________|
53 |   bat    | 
___|__________|

Answer 1

You can do it with array formula:

=AVERAGE(IF(COUNTIF($H$1:$H$4;$A$2:$A$4)>0;$B$2:$B$4))

NOTE: Because it's an array formula, it must be entered pressing CTRL+ENTER+SHIFT or it won't work!

UPDATE:: The formula will work of you do =AVERAGE(IF(COUNTIF($H$1:$H$4;A:A)>0;B:B)) but it may overload file and create performance issues.

In that case, I would use a helper column, indeed:

Formula in helper column is (and drag down)

=COUNTIF($H$1:$H$4;A2)

To get average, then use AVERAGEIF:

=AVERAGEIF(C:C;1;B:B)

This will not overcharge your calculation as heavy as an array formula would do if you want to use full column