Array Formulae 
Importance of Speed  Faster Calculations  Microsoft's Advice  Bottlenecks/Size 
Lookups  Dynamic Ranges  Totalling  Generating Workbooks 
MultiLevel Calculations  Array Formulae  SUMIF Example  Worksheet Links 
Array FormulaeArray formulae are one of Excel’s most powerful features, although not always the easiest to use. Look at Bob Umlas's excellent paper at http://www.emailoffice.com/excel/arraysbobumlas.html for many examples of Array formulae, and also Chip Pearson's page at http://www.cpearson.com/excel/array.htm . This article is translated to SerboCroatian language at WebHostingGeeks.com. Singlecell array formulae are evaluated multiple times, depending
on the number of cells referred to in the formula. This can take significant
time, and may or may not be faster than the alternatives. How Microsoft Excel Expands Array FormulasWhen you use an array constant in a formula or function, the other components or arguments should have the same dimensions as the first array. If necessary, Microsoft Excel expands the necessary components to the required dimensions. Each component must have the same number of rows as the component with the greatest number of rows, and the same number of columns as the component with the greatest number of columns. For example, in the formula =SUM({ 1,2,3 }*4), one component is a lby3 array and the other is a single value. In evaluating this formula, Microsoft Excel automatically expands the second component to a lby3 array and evaluates the formula as =SUM({ 1,2,3}*{4,4,4}). The formula's result equals 24, which is the sum of 1*4, 2*4, and 3*4. The following table shows how array components or arguments are expanded.
You should enter an array formula in a range of cells with the same dimensions as the resulting array produced by the formula. Microsoft Excel can then place each value in the resulting array into one cell of the array range.
For example, the formula ={ 1,2,3}*{2,3,4} produces the lby3 array {2,6,12}.
Special Functions Help You Work with ArraysMicrosoft Excel includes some worksheet functions that return arrays of values or require either array constants or references to cell ranges as arguments. For example, you want to compute the trend of your monthly sales over the last six months. That trend is described not by one value, but by six—the trend values corresponding to the sales in each of the last six months. Microsoft Excel provides a worksheet function, TREND, that performs this computation for you and produces the six values. You enter the trend formula into 6 adjacent cells using Control/Shift/Enter. The Microsoft Excel functions that work with arrays are included in the following table.
There are also other functions that can take either singlevalue arguments or array arguments. These functions return different results when used with array arguments. For more information about functions, click the Function Wizard button on the Standard toolbar or see online Help. Caution Before you enter a function on a worksheet, you need to know what the function returns. For example, the MINVERSE (matrix inverse) function returns an array the same size as its argument. Another function, TRANSPOSE, returns an array with the opposite dimensions of its argument. Make sure you allow space on your worksheet for the resulting array. Minimise the Number of Array Operations
Array formulae SUM with multiple conditionsOne frequent use of Array formulae is to do a sum with multiple conditions. This relatively easy to do, particularily if you use Excel's Conditional Sum Wizard, but often very slow. Usually there are alternative ways of getting the same result, but much faster. Two good methods of speeding up multiple condition SUMs are:
See SUMIF example for a worked example. The downloadable FastExcel Sample Problem also has an example which shows the method and the considerable time saving which is often achievable. Using SUMPRODUCT for multiple condition array formulae.There are some advantages to using SUMPRODUCT rather than SUM array formulae:
You can use SUMPRODUCT for multiple condition array formulae as follows: SUMPRODUCT((Condition1),(Condition2),RangetoSum) where Condition1 & 2 are conditional expressions such as $A$1:$A$10000<=$Z4. Because conditional expresssions return True or False rather than numbers they need to be coerced to numbers inside the SUMPRODUCT function. You can do this using two minus signs () or by adding 0 (+0) or by multiplying by 1 (*1). Using  is very slightly faster than +0 or *1. Bug in Excel 2007 SP2, subsequently fixed in 12.0.6545.5000:
If you use the 2 minus signs () syntax in an array formula or SUMPRODUCT
formula with a direct reference to a volatile function in Excel 2007
SP2 the formula does not recalculate when the vloatile function referred
to recalculates. (this works correctly in all other Excel versions).
For Example: Note that the size and shape of the ranges or arrays used in the conditional expressions and range to sum must be the same, and cannot contain entire columns. You can also directly multiply the terms inside SUMPRODUCT rather than separate them by commas: SUMPRODUCT((Condition1)*(Condition2)*RangetoSum) But this is usually slightly slower than using the comma syntax,
and gives an error if the range to sum contains a text value. Using SUMPRODUCT to multiply and add ranges and arrays.In cases like weighted average calculations where you need to multiply a range of numbers by another range of numbers and sum the results using the comma syntax for SUMPRODUCT can be 2025% faster than an array entered SUM: {=SUM($D$2:$D$10301*$E$2:$E$10301)} =SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301) =SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301) These three formulae all produce the same result, but the third formula using the comma syntax for SUMPRODUCT only takes about 77% of the time to calculate that the other two formulae need. Use DSUM instead of multiple condition array formulae.If you only have a few array formulae sums with multiple conditions you may be able to use DSUM instead. DSUM is significantly faster than equivalent array formulae. The disadvantage of DSUM is that the criteria have to be in a separate range, which makes it impractical to use and maintain in many circumstances. Use FastExcel AVLOOKUPS.You can use the FastExcel Version 2 AVLOOKUPS function inside a SUM function to return multiple rows and SUM them. If you are doing multiple conditional sums, particularly on sorted data, AVLOOKUPS can be significantly faster than array formulae. Array and Function Calculation BottlenecksExcel’s calculation engine is optimised to exploit array formulae and functions that reference ranges. However some unusual arrangements of these formulae and functions can sometimes, but not always, cause significantly increased calculation time. If you find a calculation bottleneck involving arrayformulae and range functions look for:
Advantages and Disadvantages of Array FormulaePlus points of Array Formulae:
Minus points of Array Formulae:



