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.

create one dimensional array formula in excel

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

create one dimensional horizontal array formula in excel

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

result of array formula in Excel

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.

create two dimensional array formula in Excel

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

result

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.

using TRANSPOSE function with array formula in excel

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

creation of array formula using SUM function in Excel

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

array formula with MAX Function in Excel

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

result


Similar Readings


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.

array formula for multiple rows in Excel

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

summing up nth largest/smallest values after creating an array formula in Excel

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

create array formula with multiple criteria

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

Applying IF function with array formula and AND operator in Excel

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

Applying IF function with array formula and OR operator in Excel

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

create dynamic array formula with UNIQUE Function in Excel

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

create dynamic array formula with FILTER Function in Excel

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

create dynamic array formula with SORT Function in Excel

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

create dynamic array formula with SORTBY Function in Excel


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.

create dynamic array formula with SEQUENCE Function in Excel

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

create dynamic array formula with RANDARRAYFunction in Excel

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

practice section


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo