where "group" (B5:B14), "region" (C5:C14), and "sales" (D5:D14) are named ranges.
Like the COUNTIFS and SUMIFS function, the AVERAGEIFS function is designed to handle multiple criteria which are entered in [range, criteria] pairs. As long as this information is supplied correctly, the behavior of AVERAGEIFS is fully automatic.
In the example shown, we have group values in column F and region values in column G. We use these values directly by using cell references for criteria.
The first argument holds the range of values to average:
In the example shown, the formula in E5 is based on the AVERAGE function : = AVERAGE ( B5:D5 ) // returns 60 The result is 60, since (90+90+0)/ 3 = 60. To filter out the zero from the calculated average, the formula in E6 uses the AVERAGEIF function...
The AVERAGEIFS function can average ranges based on multiple criteria. In this case, we configure AVERAGEIFS to average amounts by month using two criteria: (1) match dates greater than or equal to the first day of the month, (2) match dates less...
The Excel AVERAGEIFS function calculates the average of numbers in a range that meet one or more criteria. The criteria used for AVERAGEIFS can include logical operators (>,,=) and wildcards (*,?) for partial matching....
Excel Formula Training
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.