Use the AVERAGEIFS function to get the average (mean) of certain row(s) in the table, based on a condition (or more)

The syntax is:

AVERAGEIFS(column_to_retrieve_value_from, column_to_search, condition [, default_message])
column_to_retrieve_value_from = the column where the values will be retrieved from (T1!A, T2!B, etc)
column_to_search = the column that will be searched
condition = the condition that will be applied on "column_to_search"
default_message (optional) = if no results meet the condition, return this message (for example 'No Results Found'), otherwise '...' will be shown.
AVERAGEIFS(T1!A, T1!B, ">5", 'No Results Found')
will return the average (mean) of all the values in column A of table T1 for which the corresponding value in table T1 column B is greater than 5
so for the table:
100 2
200 10
300 20
400 30
AVERAGEIFS(T1!A, T1!B, ">10") will return the mean of 200, 300, 400, so the end result will be 300.

You can also have multiple conditions:

AVERAGEIFS(T1!A, T1!B, ">10", T1!C, 3)
This will return average (mean) of the values in column A for which the value in column B is greater than 10 and the value in column C is 3.

See the Tables page for more tips on how to work with tables.

Need Help?
Already checked the help docs? We're quick to answer and friendly
We'll be back with a reply very soon.
Send message