Perhaps one of the most useful yet least known features of Excel is its array formulas. Essentially, array formulas are standard Excel expressions which are evaluated over a range of cells, rather than just a single cell.

In my case, I needed to aggregate the status of various DevDoctor projects, tracked in an Excel workbook:

Each row against a status (D5:D9) must aggregate a count of that status from a “status” column (here, column F). Using standard formulas alone, this is not possible (unless you resort to VBA scripting); however, using array formulas, this becomes a breeze.

The formula in cell D5 (counting the projects in Pending status) is:

=SUM(IF((F$12:F$42=C5),1,0))

When you press CTRL+SHIFT+ENTER on this formula, Excel converts it to an array formula, and indicates this with curly braces:

The expression is slightly strange, with a conditional being evaluated over a range of cells (F$12:F$42), rather than the usual single cell. In effect Excel calculates the inner IF expression once for each cell in the range, and the outer SUM expression then produces the required summation.