The Excel AVERAGEIFS function calculates the average of 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.
The AVERAGEIFS function can calculate an average based on more than one criteria. The first range given to AVERAGEIFS is the range containing numbers to average. Criteria are supplied in pairs [range, criteria] and only the first pair is required. For each additional criteria, supply another range/criteria pair. The criteria given to AVERAGEIFS are applied with AND logic. Data must meet all criteria to be included in the average. Criteria can include wildcards.
To average values in A1:A10, where B1:B10 = "A", and C1:C10 > 5, you can use:
To calculate an average, excluding blank values in a criteria range, you can use the AVERAGEIFS function and a simple logical expression. In the example shown, the formula in F6 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:
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 numbers based on multiple criteria, you can use the AVERAGEIFS function. In the example shown, the formula in I5 is:
where "group" (B5:B14), "region" (C5:C14), and "...
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.