where “group” (B5:B14), “region” (C5:C14), and “sales” (D5:D14) are named ranges. 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 restrict calculation by group we provide: To restrict calculation by region we use: The result in cell J5 is 105:
Without named ranges
Named ranges are used for readability only. The equivalent formula in J5, without named ranges and with criteria values hardcoded is:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
