How to Create an Array Formula in Excel?

Array formulas in excel are important tools. A single formula can perform multiple calculations and replace thousand of useful formulas. Most of us have never used array functions in our excel sheet just because we don’t know how to use these formulas.

Array formulas is one of the most confusing excel features to learn. The aim of this tutorial is to make you learn all functions in a smooth way. In this article, we will learn about array, array formulas, how to enter array formulas into your excel sheet, and how to use array constant and array functions.

Array in Excel 

First, we need to know what the term “array” means. an array is a single row or column, or in multiple rows and columns.

For Example, if you put your monthly expenditure into an array format, it would look like this. 

{“Rent”,”Travel”,”Food”,”Petrol”}

Then, if you select cells A1 through D1, Enter the above array preceded by an equal sign (=) in the formula bar and press CTRL + SHIFT + ENTER, you will get the following result:

Array Formula in Excel

The difference between array formulas and regular formulas is that an array formula processes several values instead of just one. In layman’s words, an array formula in excel evaluates all individual values in an array and performs multiple calculations on several items. Not only can an array formulas deal with several values simultaneously, it can also return values at a time. So, the result returned by an array formula is also an array.

Example 1: Addition

You have some items in column A, and other items in column B, column C. column D and if you want to know the total expenditure add all these.

You can do this simple addition by entering (=A2+B2+C2 +D2) in the formula bar, and then you will get the final result.

Once you do this, Microsoft excel surrounds the formula with {curly braces}, which is a visual indicator of an array formula. what the formula does is add the value in each individual row of the specified array (cell A2 through D6).

This simple example shows how powerful an array formula can be. When working with hundreds and thousands of rows of data, just think how many times you can save by entering one array formula in a single cell.

Example 2: Multiplication

You have a huge number to multiply how you can do that by just enter “=” sign and then select the cell you want to multiply. 

You can do this simply by entering (=A2*B2*C2*D2)

Creating a Simple Interest Calculator in Excel

To get the interest just insert =(principal*rate of interest*time)/100 into interest 

You have principal in column A, rate of interest in column B, and time in column C and you want to know interest. You can do this simple addition by entering ( =(A2*B2*C2)/100 ) in the formula bar, and then you will get the final result.

If you want to know the total amount just enter (=D2+A2) into the formula bar and you will get the result.

Excel Formulas can make use of array Constants

Let’s look at how you may utilize arrays in formulas to complete your practical chores now that you are familiar with the idea of array constants.

Example 1: Add together the range’s greatest and smallest N values.

The first step is to create a vertical array constant that contains all the integers you wish to sum. The array constant is 1, 2, 3, for instance, if you wish to add together the three smallest or largest numbers in a range.

The next step is to choose either the LARGE or SMALL function, define the complete range of cells in the first parameter, and then add the array constant in the second. Finally, incorporate it into the SUM function as follows:

Add together the top three figures: =SUM(LARGE(range, {1, 2, 3}))

Add up the first three digits: =SUM(SMALL(range, {1, 2, 3}))

Since you are inputting an array formula, don’t forget to press Ctrl + Shift + Enter to obtain the following outcome.

You can compute the average of N least or greatest values in a range in a similar way:

The top three numbers are averaged as follows: =AVERAGE(LARGE(range, 1, 2, 3))

The bottom three numbers’ average is given by =AVERAGE(SMALL(range, 1, 2, 3)).

Example 2. Array formula to count cells with multiple conditions

Suppose, you have a list of orders and you want to know how many times a given seller has sold given products.

The easiest way would be using a COUNTIFS formula with multiple conditions. However, if you want to include many products, your COUNTIFS formula may grow too big in size. To make it more compact, you can use COUNTIFS together with SUM and include an array constant in one or several arguments, for example:

=SUM(COUNTIFS(range1, “criteria1”, range2, {“criteria1”, “criteria2”}))

The real formula may look as follows:

=SUM(COUNTIFS(B2:B9, “sally”, C2:C9, {“apples”, “lemons”}))

Due to the fact that the purpose of the example array is to illustrate the method, it only has two elements. As long as the entire formula length does not exceed 8,192 characters in Excel 2019–2007 (1,024 characters in Excel 2003 and below) and your machine is capable of handling huge arrays, you are free to incorporate as many elements as your business logic dictates in your actual array formulae. For more information, please refer to the array formula limits.

The advanced array formula SUM and VLOOKUP with an array constant finds the total of all matching values in a table.

Excel Array Formula can use the AND and OR Operators

Using AND or OR logic, an array operator instructs the formula on how to handle the arrays. The multiplication symbol is the asterisk (*), which serves as the AND operator. If ALL of the conditions evaluate to TRUE, Excel is told to return TRUE.
The plus sign (+) is the operator for OR. If ANY of the conditions in a given expression evaluates to TRUE, it returns TRUE.
The formula for arrays with the AND operator

When YARUD is the salesperson and EGGS are the product, we can calculate the total sales as follows:

=SUM (( A2:A18=”YARUD”) * (B2:B18=”EGGS”) * (C2:C18))

The items of the three arrays are technically multiplied in identical spots using this formula. As a result of comparing A2:A18 to “YARUD” and B2:B18 to “EGGS,” the first two arrays are represented by TRUE and FALSE values. The sales figures from the range C2:C18 are included in the third array. Multiplication changes TRUE and FALSE to 1 and 0, respectively, just like any other mathematical operation. When one or both of the conditions are not met, the resultant array has zero because multiplication by 0 always yields zero. The equivalent member from the third array enters the final array (for example, 1*1*C2 = 10) if both requirements are satisfied. Therefore, multiplying yields the array 10; 0; 0; 30; 0; 0; 0 as the end result. The SUM method totals the elements of the array and returns a result of 45.

Excel array formula with OR operator

The following array formula with the OR operator (+) sums all sales where the seller is Mike OR the product is his Apple.

=SUM(IF(((A2:A18=”AQUIB”) + (B2:B18=”EGGS”)), (C2:C18)))

This formula adds elements from the first two arrays (these are the conditions to test) and returns TRUE (>0) if at least one condition evaluates to TRUE. FALSE (0) if all conditions evaluate to FALSE. IF then checks if the result of the addition is greater than 0, and if so, SUM adds the corresponding elements of the third array (C2:C18).

Tip. With the latest versions of Excel, you don’t need to use array formulas for this type of task. A simple SUM formula can do it perfectly. Still, the AND and OR operators in array formulas prove useful in more complex scenarios, not to mention a very good mental exercise.

Double Unary Operator in Excel array formula

If you’ve ever used array formulas in Excel, you may have come across some with two hyphens (–) and wondered what they’re used for.
The double dash, technically called the double unary operator, is used to convert non-numeric Boolean values ​​(TRUE/FALSE) returned by some expressions to 1s and 0s that array functions understand.
The following example is hopefully easier to understand. Suppose you have a list of dates in column A and want to know how many dates occur in January regardless of the year.
The following formula will work.

=SUM(–(month(A2:A10)=1))

This is an Excel array formula, so don’t forget to press Ctrl + Shift + Enter to complete. If you’re interested in a different month, replace 1 with the appropriate number. For example, 15 is February, 15 is his March, etc. To make the formula more flexible, you can specify the month number in the cells as the below screenshot shows.

Let’s analyze how this array formula works. The MONTH function returns the month of each date in cells A2 through A10, represented by a sequential number that produces the array.
Each element of the array is then compared to the value in cell C1 (number 1 in this example). The result of this comparison is an array of Boolean values ​​TRUE and FALSE. As you know, you can select a particular part of an array formula and press F9 to see what that part corresponds to.
Select a portion of the array formula and press F9 to see what that portion corresponds to. Finally, we need to convert these Booleans into 1s and 0s that the SUM function understands. For that, you need the double unary operator. The first unary forces TRUE/FALSE to -1/0 respectively. The second unary negates the value. That is, it flips the sign and converts it to +1 and 0 that most Excel functions understand and manipulate. If I remove the double unary from the above formula, it doesn’t work. We are confident that this short tutorial has proven to be helpful on your journey to mastering array formulas in Excel. Next week, we’ll continue with Excel Arrays, focusing on advanced formula examples. Please look forward to it. Thank you for reading.

How to Enter Array Formula in Excel (CTRL+SHIFT+ENTER)

As you already know, the combination of the 3 keys CTRL + SHIFT + ENTER is a magic touch that turns a regular formula into an array formula. when entering an array formula in excel, there are 4 things to keep in mind. 

1. Once you have finished typing the formula and simultaneously pressed the key CTRL SHIFT ENTER, excel automatically encloses the formula between {curly braces}. When you select such cells, you can see the braces in the formula bar, that gives you a clue that an array formula is in there.
2. Manually typing the braces around a formula won’t work. You must press the formula, the ctrl + shift + enter shortcut to complete an array function 
3. Every time you edit an array formula, the braces disappear and you must press ctrl + shift + enter again to save the changes.
4. If you forgot to press ctrl + shift+ enter, your formula will behave like a usual formula and process only the first value in the specified array.

Use the F9 Key to Evaluate Portion of an Array Formula

When working with array formulas in excel, you can observe how they calculate and store their items (internal array) to display the final result you see in the cell. To do this, select one or several arguments within a function’s parenthesis, and then press F9 key. To exit the formula evaluation mode, press the Esc key. In the above example, to see the sub-total of all products, you select =A2+B2+C2 +D2 and press f9 and to get the result.