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.

**Table of Contents**hide

## 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**.

`={"Apple";"Orange";"Kiwi";"Tomato";"Banana"}`

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**.

`={"Apple", "Orange", "Kiwi", "Tomato", "Banana"}`

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**.

`={"Apple", "Orange", "Kiwi", "Tomato", "Banana";3981,3849,3003,2652,1275}`

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**.

`=TRANSPOSE(B4:C8)`

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**.

`=SUM(C4:C8*D4:D8)`

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**.

`=MAX(D4:D8-C4:C8)`

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**.

`=(D4:D8-C4:C8)*E4:E8`

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**.

`=SUM(LARGE(C4:C8,{1,2}))`

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.

`=SUM(SMALL(C4:C8,{1,2}))`

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**.

`=SUM(COUNTIFS(B4:B12,"Lily",C4:C12,{"Mango","Orange"}))`

**Formula Breakdown**

**COUNTIFS(B4:B12,”Lily”,C4:C12,{“Mango”,”Orange”}) →**becomes**Output → {0,1}**

**SUM(COUNTIFS(B4:B12,”Lily”,C4:C12,{“Mango”,”Orange”}))****→**becomes**SUM({0,1})****Output → 1**

- 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**.

`=SUM(IF(((B4:B12="David")*(C4:C12="Mango")),(D4:D12)))`

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**.

`=SUM(IF(((B4:B12="David")+(C4:C12="Mango")),(D4:D12)))`

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**.

`=UNIQUE(B4:B12)`

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**.

`=FILTER(B4:D12,B4:B12="Katy")`

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**.

`=SORT(B4:D12,1,-1)`

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**.

`=SORTBY(B4:D12,C4:C12,1)`

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**.

`=SEQUENCE(9,1,1,1)`

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**.

`=RANDARRAY(9,1,1,9,TRUE)`

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.