For those who do not have a background in programming or mathematics, the expression Array may not be familiar.
So what exactly is an Array?
For our purposes, an Array is simply a set of values which can be stored in a formula, a range of cells, or the computers memory.
The size of an Array can range from two values to thousands.



There are several different types of Arrays used by Excel when working with formulas:
An Array stored in a Worksheet in a range of cells: For example, when the SUM function sums the values stored in a range of cells, it is treating those values as an Array.



Instead of entering cell addresses to enable a formula to operate on the values stored in those locations, you may enter an Array of values into the SUM function arguments: =SUM(1,2,3,4,5).
Or
Enter an Array enclosed in brackets into the formulas argument. For example, use the MATCH function to return the position of the number 10 in an Array of values: =MATCH(10,{3,7,10,15,20}). The result = 3



Formulas such as SUMPRODUCT utilize computer memory to store values temporarily while calculating complicated math problems. These values are stored in an Array.


To add the total sales amount of 3 items when the quantities sold are 10, 20, and 30 and the sale prices are $3, $4, and $5 respectively, the SUMPRODUCT formula stores each multiplication product in an Array (Array size is 3) and then adds the three values from the Array.
The SUMPRODUCT formula: =SUMPRODUCT(A1:A3,B1:B3),
Result - total sales=$260.



As explained in the previous section, many formulas create Arrays when they need to store values during calculations. However, an Excel user may create a formula that deliberately enforces the program to open an Array/Arrays to store values.


Use the SUM function to return total sales (see previous example). The formula will now look like this: {=SUM(A1:A3*B1:B3)}, Result- total sales=$260.


Enter the formula, select the cell, press F2, and then simultaneously press Ctrl+Shift+Enter.