Entering Array Formulas in Excel
The term “array formula” itself is a fuzzy one. It’s true that many array formulas in Excel are intended to fill an array of cells on the worksheet. But it’s also true that many array formulas are intended to occupy one cell only.
You might find it helpful to think of an array formula as one that processes one or more arrays of data — arrays that might or might not appear on the worksheet. If the arrays aren’t visible on the sheet, they’re used in Excel’s internal processing locations, away from prying eyes.
You need to inform Excel that what you’re entering is an array formula, not a normal one such as this:
To enter that formula and the function it employs, you select a cell, type the formula, and press Enter. Suppose you want to array enter a formula such as this one:
To do so, you select a cell, type the formula, and simultaneously hold down the Ctrl and the Shift keys as you press Enter. If you have done things right, the formula shows up in the formula bar surrounded by curly brackets. And the cell where you array-entered the formula will show the average of the values, perhaps sales revenues, in A2:A25 for any record with the text “Zig” in B2:B25.
AVERAGEIF and SUMIF are two of the functions coded by Microsoft to give you an alternative to the array formula methods. Maybe I’m just being reactionary, but I’ve always preferred the array formulas. I think it gives me more control over what’s going on.