Array formulas in Excel

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.

One thought on “Array formulas in Excel

Join the discussion...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s