where "group" (B5:B14), "region" (C5:C14), and "sales" (D5:D14) are named ranges.
How this formula works
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:
To get the average of a set of numbers, excluding or ignoring zero values, use the AVERAGEIF function. In the example shown, the formula in E6 is: = AVERAGEIF ( B6:D6 , "<>0" ) How this formula works In the example shown, the formula...
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: = AVERAGEIFS ( amounts , dates , ">=" & F5 , dates , "<="...
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....
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.