How to Create Array Formula in Excel (10 Suitable Examples)

If you are looking for ways to create an array formula in Excel, then this article is for you. After going through this article, you will get 10 different examples regarding this topic. Moreover, examples of dynamic array formulas are also given here. So, let’s start with the main article.

Download Workbook

What Is an Array Formula?

An array is an accumulation of different types of data. Using an array formula, you can easily do multiple calculations at once. So, using an array formula can be helpful to reduce your consumption of time while working on Excel.

10 Examples to Create Array Formula in Excel

In the following 10 examples, we will show you the various ways of creating array formulas in Excel. According to the needs of the examples, we have used different datasets.
We have used Microsoft Excel 365 version for creating this Excel. So, we are pressing ENTER here, but if you are using any other versions then you have to press CTRL+SHIFT+ENTER.

Example-1: Create One Dimensional Array Formula with Constant Values in Excel

Here, we will make a list of products in the Product column vertically using an array formula.

• Type the following formula in cell B4.

Here, we have used the names of different products inside of inverted commas and separated each product with semicolons. Finally, we have enclosed the whole list with the second brackets.

• After pressing ENTER, you will get the following list of products vertically in the Product column.

If you want to enter the name of the products horizontally, then adopt a small change in your formula like the following technique.

• Type the following formula in cell C3.

Here, we have used the names of different products inside of inverted commas and separated each product with commas. Finally, we have enclosed the whole list with the second brackets.

• Finally, the following results will appear after pressing ENTER.

Read More: How to Create a Formula in Excel (5 Ways)

Example-2: Create Two Dimensional Array Formula with Constant Values in Excel

In this section, we will create a two-dimensional array formula to enter a list of products with their corresponding sales values.

• Write down the following formula in cell C3.

Here, the name of the products and the sales values are separated with commas. But the final product name is separated by a semicolon from the sales value of the first product.

• Press ENTER.

In this way, you will get a list of products with their corresponding sales values.

Read More: How to Create a Complex Formula in Excel (with Easy Steps)

Example-3: Using TRANSPOSE Function with Array Formula to Reverse Rows and Columns

Here, we will be inverting the columns of the first dataset into rows in the following table. For this purpose, we will use the TRANSPOSE function.

• Type the following formula in cell C11.

Here, B4:C8 is the range of the rows which we want to transform into columns.

• After pressing ENTER, you will get the following converted table.

Read More: How to Apply Formula to Entire Column Using Excel VBA

Example-4: Creating Array Formula with SUM Function in Excel

In the following dataset, we will first multiply the sales values with their corresponding discounts and then add up the discounted sales values. This work can be done with a single formula using an array formula with the SUM function.

• Type the following formula in cell D9.

Here, C4:C8 is the range of the sales, and D4:D8 is the range of the discounts.

After pressing ENTER, we have got the total discounted sales value.

Read More: How to Create an Excel Formula to Subtract (10 Examples)

Example-5: Generating Array Formula with MAX Function in Excel

In this section, we will use the MAX function with an array formula to get the maximum profit value after subtracting the sales values from the cost prices.

• Write down the following formula in cell D10.

Here, C4:C8 is the range of Cost Prices, and D4:D8 is the range of Sales values.

• Press ENTER.

In this way, you will get the maximum profit value.

Similar Readings

• How to Create a Formula in Excel to Change Date by 1 Year (3 Methods)
• Create a Formula to Calculate Percentage in Excel
• How to Create a Formula in Excel That Will Place the Word Yes (7 Ways)

Example-6: Creating Array Formula for Multiple Rows in Excel

In this example, we will be calculating the bonus earned by the employees by multiplying the profits of the products with their corresponding % Bonuses.

• Use the following formula in cell F4.

Here, C4:C8 is the range of Cost Prices, D4:D8 is the range of Sales values, and E4:E8 is the range of the % Bonuses.

• After pressing ENTER, you will get bonuses for each product finally.

Read More: How to Create a Formula in Excel for Multiple Sheets (4 Methods)

Example-7: Summing up Nth Largest or Smallest Numbers in Excel

Here, we will be summing up the largest two of the sales values using the LARGE function in an array formula. Also, for calculating the smallest of the two sales values we will be using the SMALL function in an array formula.

• Enter the following formula in cell E4.

Here, C4:C8 is the range of the sales values, and {1,2} is for extracting the largest two values.

• Type the following formula in cell E6 to get the sum of the lowest two sales values.

Here, C4:C8 is the range of the sales values, and {1,2} is for extracting the smallest two values.

Example-8: Creating Array Formula with Multiple Criteria in Excel

Here, we will count the total number of times the products Mango, and Orange sold by Lily by using the COUNTIFS function in an array formula.

• Type the following formula to count the number of presence of the following products corresponding to the SalesPerson Lily.

• Press ENTER.

In this way, you will get the total number of presence of these products for the corresponding person as 1.

Read More: How to Create a Formula in Excel for Multiple Cells (9 Methods)

Example-9: Using IF Condition with Array Formula and AND Operator in Excel

In this section, we will be calculating the total sales value for Mango sold by David using the IF function and the AND operator within an array formula.

• Write down the following formula in cell D15.

When both of the conditions B4:B12=”David” and C4:C12=”Mango” will be fulfilled for a row, then the corresponding sales values will be extracted and then the values will be added up.

• After pressing ENTER, you will have the following result.

Example-10: Use of IF Condition with Array Formula and OR Operator in Excel

In this example, we will be calculating the total sales value for Mango sold by David using the IF function and the OR operator within an array formula.

• Write down the following formula in cell D15.

When any of the conditions B4:B12=”David” and C4:C12=”Mango” will be fulfilled for a row, then the corresponding sales values will be extracted and then the values will be added up.

• Press ENTER.

Finally, you will get the following result.

Dynamic Array Formula in Excel

Now, we will discuss the uses of the 6 dynamic functions which are an extension of the Microsoft Excel 365 version.

Example-1: Using UNIQUE Function in Excel

Here, we will extract the unique names of the SalesPerson column using the UNIQUE function.

• Enter the following formula in cell B15.

Finally, you will have unique names.

Example-2: Use of FILTER Function in Excel

Here, we will extract the data for the SalesPerson Katy using the FILTER function.

• Type the following formula in cell B15.

Here, B4:D12 is the range, and B4:B12=”Katy” is the criterion on the basis of which we are filtering.

• Press ENTER.

In this way, you will get the filtered table.

Example-3: Applying SORT and SORTBY Functions in Excel

Here, we will be sorting data using the SORT and SORTBY functions.

• Use the following function in cell B15.

Here, B4:D12 is the range of the dataset, 1 is the column number on the basis of which we are sorting, and -1 is for descending order.

• After pressing ENTER, you will get the sorted dataset like the following figure where it is sorted in descending order depending on the SalesPerson

The same work can be done also by using the SORTBY function.

Here, B4:D12 is the range of the dataset, C4:C12 is the column range on the basis of which we are sorting, and 1 is for ascending order.

Example-4: Applying SEQUENCE Function in Excel

Here, we will put some serial numbers from 1 to 9 in a sequence by using the SEQUENCE function.

• Enter the following formula in cell B4.

Here, 9 is the total rows, 1 is the column number, 1 is the starting number, and 1 is the step number.

Example-5: Utilizing RANDARRAY Function in Excel

For creating random serial numbers, here we will be using the RANDARRAY function.

• Enter the following formula in cell B4.

Here, 9 is the total rows, 1 is the column number, 1 is the minimum number, and 9 is the maximum number, TRUE is for integer.

Note:

The serial numbers generated by this function will be automatically changed after each time you refresh in Excel.

Array Formula Not Working in Excel?

If the array formulas are not giving results in your case, then make sure the version you are using. If it is any version except for Microsoft Excel 365 version, then you have to press CTRL+SHIFT+ENTER for displaying the results.

To know more array related problems and solutions you can click on this link.

Practice Section

To practice by yourself, we have created a Practice section n the right side of each sheet.

Conclusion

In this article, we have discussed various ways to create an array formula in Excel. Hope these examples will help you a lot. If you have any further queries, then leave a comment below.

Related Articles

• How to Create a Formula with Structured References in Excel
• Create a Nested Formula in Excel (3 Easy Examples)
• How to Apply Formula to Entire Column Without Dragging in Excel
• How to Create a Formula in Excel without Using a Function (6 Approaches)