- Introduction
- Understanding Formulas
- Creating a Simple Formula
- Creating a Formula Using Formula AutoComplete
- Editing a Formula
- Understanding Cell Referencing
- Using Absolute Cell References
- Using Mixed Cell References
- Using 3-D Cell References
- Naming Cells and Ranges
- Entering Named Cells and Ranges
- Managing Names
- Simplifying a Formula with Ranges
- Displaying Calculations with the Status Bar
- Calculating Totals with AutoSum
- Performing One Time Calculations
- Converting Formulas and Values
- Correcting Calculation Errors
- Correcting Formulas
- Auditing a Worksheet
- Locating Circular References
- Performing Calculations Using Functions
- Creating Functions
- Creating Functions Using the Library
- Calculating Multiple Results
- Using Nested Functions
- Using Constants and Functions in Names
This chapter is from the book
This chapter is from the book
This chapter is from the book
Calculating Multiple Results
An array formula can perform multiple calculations and then return either a single or multiple result. For example, when you want to count the number of distinct entries in a range, you can use an array formula, such as {=SUM(1/COUNTIF(range,range))}. You can also use an array formula to perform a two column lookup using the LOOKUP function. An array formula works on two or more sets of values, known as array arguments. Each argument must have the same number of rows and columns. You can create array formulas in the same way that you create other formulas, except you press Ctrl+Shift+Enter to enter the formula. When you enter an array formula, Excel inserts the formula between { } (brackets).
Create an Array Formula
Click the cell where you want to enter the array formula.
Click the Formulas tab.
Type = (an equal sign).
Use any of the following methods to enter the formula you want.
- Type the function.
- Type and use Formula AutoComplete.
- Use the Function Wizard.
- Use button in the Function Library.
Press Ctrl+Shift+Enter.
{ } (brackets) appear around the function to indicate it’s an array formula.
)