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 reply very soon at
Send message