The Excel AVERAGEIFS function computes the average of the numbers in a range that meet one or more supplied criteria. The criteria in AVERAGEIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
criteria2 - [optional] The criteria to use on range2.
AVERAGEIFS averages cells in a range that match supplied criteria. Unlike the AVERAGEIF function, AVERAGEIFS can apply more than one set of criteria, with more than one range. The first range is the range to be averages. The criteria is supplied in pairs (range/criteria) and only the first pair is required. For each additional criteria, supply an additional range/criteria pair. Up to 127 range/criteria pairs are allowed.
Note the order of arguments is different between the AVERAGEIFS and AVERAGEIF functions. The range to average is the first argument in AVERAGEIFS, but the third argument in AVERAGEIF.
AVERAGEIFS returns the #DIV0! error value when no criteria are met
Each additional range must have the same number of rows and columns as the avg_range.
Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not. For example: 100, "100", ">32", "jim", or A1 (where A1 contains a number).
The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.
To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
To average by month, you can use a formula based on the AVERAGEIFS function, with help from the EOMONTH function.
In the example shown, the formula in F4 is:
To average response times by month, you can use a formula based on the AVERAGEIFS function, together with the EOMONTH function.
In the example shown, the formula in G5 is:
The Excel AVERAGE function returns the average of values supplied as multiple arguments. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.
The Excel AVERAGEIF function computes the average of the numbers in a range that meet the supplied criteria. The criteria for AVERAGEIF supports logical operators (>,,=) and wildcards (*,?) for partial matching....
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.