In Lesson F we saw that when confronted by a formula that mentions several cells where one would be more usual, Excel can often make sense of the expression. It will resort to matching columns to pick out one of the cells to act on. In Lesson I we saw that, if the spreadsheet layout demands it, Excel can match rows too.
In the first example below, a name Costs has been defined to refer to the shaded cells. The other populated cells all say =Costs. That formula refers to five cells, but Excel can only fit one value in each cell, so it chooses one by matching columns. In columns A and G, no column match is possible that intersects with the five yellow cells, so an error is displayed.
.
In the second example below, the formula in the populated cells says =SUM(Costs). Though there are five cells in costs, the action of the SUM is to distill them into a single value, the total of the five cells. Now that that there is just one value, it can be fitted in any cell, and no column match is necessary.
SUM is not the only function that the values in the cells it acts on into a single value. Others are PRODUCT, AVERAGE, VAR, STDEV, MIN, MAX, INDEX, NPV and IRR. No column or row matching happens with these functions because the aggregating action renders it unnecessary.
See also