Excel Functions: Using Array formulas

An array is simply a series of data, which in a spreadsheet is normally stored in a range of adjacent cells. Excel however can do special things with arrays that may be used as a flexible alternative to the database functions such as DSUM.

Many functions such as SUM or MAX can incorporate selection criteria rules and may be combined with the IF function using arrays. This means that the sum or average function will be performed on only those items that meet a criteria. In other words, the values supplied as an argument to the sum function are conditional upon a value in an another column or location. The following example should give you an insight as to the potential uses of array functions.

A table of values (E5:G12) can be interrogated using the criteria values entered into cells F14 and H14 in order to extract summary information. The first query (in cell G16) returns the total number of items processed by operator 'AK' throughout the whole table. The second (G17) gives the highest number of items processed on a day in April (month 4).

The formulas in rows 1 and 2 perform the same calculation as rows 3 and 4. The multiplication character (*) acts as an 'AND' instruction. The formula in row 5 contains two different criteria.

All of these are Array formulas and this is evidenced by the curly brackets {braces}. When you enter an array formula there are three rules you must follow:

• Instead of typing <Enter> to place the function in a cell, you must type <Ctrl> + <Shift> + <Enter>. If you don't do so, the formula probably won't make sense and will cause an error.
• You can not type the Braces {}. They are entered automatically with the above key combination.
• The array ranges (for the criteria and the values) must be the same size (i.e. the same number of rows) although they need not be in adjacent columns.

These array functions can be used in a range of situations where the criterion ranges required by database functions (eg DSUM) would be unwieldy. In other situations the SUMPRODUCT, SUMIF or COUNTIF functions may be better still.

A peculiar use of an array function is to add up a range of numbers which have been entered as text. If a range of numeric values have been converted and formatted using the TEXT function they can no longer be added using the SUM function. The following formula however should do the trick {= SUM(VALUE(range_of_cells)) }. Remember to use <Ctrl> + <Shift> + <Enter>. In normal use the VALUE function converts a text number into a numeric value.

The ability of array formulas to contain a logical IF() function means that they can also incorporate the ISERROR() function and avoid errors caused when trying to SUM() a range of values which contain errors such as #DIV/0! or #N/A!. e.g. {=SUM(IF(ISERROR(C12:C16)=TRUE, 0, F6:F12)*(F6:F12))}

The SUMIF function

If there is a requirement to SUM a range of numbers and apply a single criteria, SUMIF() may be the simplest solution. It takes the form =SUMIF(range, criteria, [sum_range]) where Range is the range of cells you want evaluated, Criteria defines which cells are to be included and Sum_Range is the range of values to be summed if different to the main Range.

SUMIF() is able to incorporate wildcards (such as in row 3 above) which is an advantage over SUMPRODUCT() or array formulas.

Note that Excel will assume that the Sum_Range is the same size as the Range. In row 2 (above) the F6 Sum_Range parameter is taken to be the top of the range of cells to be summed. In row 4 the absence of a separate Sum_Range means that Excel will sum the main Range.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.