In this lesson, we will get acquainted with a single-cell array formula and analyze a good example of its use in Excel. If you are still quite unfamiliar with array formulas, we recommend that you first turn to the lesson, which describes the basic principles of working with arrays in Excel.
Applying a Single Cell Array Formula
If you read the lesson about multi-cell array formulas, then the figure below shows a table already familiar to you. This time our task is to calculate the total cost of all goods.
Of course, we can do the classic way and simply sum the values from the range of cells D2:D6. As a result, you will get the desired result:
But there are situations when doing intermediate calculations (in our case, this is the range D2:D6) does not make any sense, is inconvenient or impossible at all. In this case, a single-cell array formula comes to the rescue, which will allow you to calculate the result with just one formula. To enter such an array formula in Excel, follow these steps:
- Select the cell where you want the result to appear:
- Enter the following formula:
- Since this is an array formula, the input must be completed by pressing the combination Ctrl + Shift + Enter. As a result, we will get a result identical to the one calculated earlier.
How does this array formula work?
- This formula first multiplies the corresponding values of the two ranges:
- And based on the received data, it creates a new vertical array that exists only in the computer’s RAM:
- Then the function SUM sums the values of this array and returns the result.
Array formulas – This is one of the most complex, and at the same time useful, tools in Microsoft Excel. Single-cell array formulas allow you to perform calculations that cannot be done in any other way. In the following lessons, we will look at several such examples.
So, in this lesson, you got acquainted with single-cell array formulas and analyzed an example of solving a simple problem. If you want to learn even more about arrays in Excel, read the following articles:
- Introduction to array formulas in Excel
- Multicell array formulas in Excel
- Arrays of constants in Excel
- Editing array formulas in Excel
- Applying array formulas in Excel
- Approaches to editing array formulas in Excel