Instructions and examples of array formulas Array Formulas in Excel

The following article details array formulas and practical examples to help you better understand and apply them in your work most effectively. To use the array formula you always remember when you enter the calculation formula, press Ctrl + Shift + Enter instead of the Enter key .

1. Array formula to sum by condition, count by condition

For conditional sums we often use the sumif function but to simplify the formula try tracking the example of conditional sum using array formulas.

For example: Calculate the total number of salespeople with a number greater than 38. And calculate the total number of sales of those employees.

There are tables of data:

Instructions and examples of array formulas Array Formulas in Excel Picture 1

1.1. Counts the number of employees that sell more than 30 products

In this case, you can use the function counif but here I introduce how to use the Sum array formula. In the cell you need to calculate the following formula: = SUM (B4: B10> 30) .

Instructions and examples of array formulas Array Formulas in Excel Picture 2

Press Ctrl + Shift + Enter . Result:

Instructions and examples of array formulas Array Formulas in Excel Picture 3

Because the comparison condition returns True or False and uses the Sum function, here you add the following formula:

Instructions and examples of array formulas Array Formulas in Excel Picture 4

Press F9 -> Press Ctrl + Shift + Enter to get the results as shown below:

Instructions and examples of array formulas Array Formulas in Excel Picture 5

1.2. Conditional sum using array formulas

Calculates the total number of products sold by people who have sold more than 30 products.

Step 1: In the cell to calculate enter the formula.

Instructions and examples of array formulas Array Formulas in Excel Picture 6

Press Ctrl + Shift + Enter to get the results:

Instructions and examples of array formulas Array Formulas in Excel Picture 7

For some devices, if you enter the above formula without giving the correct results, highlight the parameter in the Sum function -> Press F9 to check the results again -> press Ctrl + Shift + Enter .

2. The array formula used in calculating the maximum difference of stock codes over days

For example: Calculating the maximum and minimum price difference of a stock code by day:

Instructions and examples of array formulas Array Formulas in Excel Picture 8

Normally you will create an extra column to find the difference then use the Min and Max functions. In this article, we will use array formulas to calculate without creating an extra column of difference prices.

Step 1: At the cell that needs to calculate the difference, enter the following formula = Max (E5: E10-D5: D10).

Instructions and examples of array formulas Array Formulas in Excel Picture 9

After entering the formula, press Ctrl + Shift + Enter to get the results. You can check by highlighting the parameters in the Max function and pressing F9 to check:

Instructions and examples of array formulas Array Formulas in Excel Picture 10

Here Max will value in an array of 6 elements.

3. Calculate Max, Min according to conditions combining array formulas

The example here calculates the largest and smallest sales of employees sold during the month. In the cell to calculate the largest sales enter the formula: = Max (If ($ B $ 5: $ B $ 10 = E6, C5: C10)) .

Instructions and examples of array formulas Array Formulas in Excel Picture 11

After entering the formula, press Ctrl + Shift + Enter to get the result:

Instructions and examples of array formulas Array Formulas in Excel Picture 12

Calculating the smallest sales is the same as the largest sales that is only replaced by the MIN function :

Instructions and examples of array formulas Array Formulas in Excel Picture 13

Similarly, you calculate for the remaining employees only to change the If condition that is compared to that employee code.

4. Find items that are delivered to agents in the North, Central and South

Instructions and examples of array formulas Array Formulas in Excel Picture 14

Using the Index function, match in combination with an array formula.

In the cell to calculate enter the following formula: = IFERROR (INDEX ($ D $ 5: $ D $ 12, MATCH ($ F $ 6 & $ G $ 5, $ B $ 5: $ B $ 12 & $ C $ 5: $ C $ 12,0) ), "") .

Instructions and examples of array formulas Array Formulas in Excel Picture 15

After entering the formula, press Ctrl + Shift + Enter to get the results:

Instructions and examples of array formulas Array Formulas in Excel Picture 16

Similarly dragging the formula to the remaining cells results:

Instructions and examples of array formulas Array Formulas in Excel Picture 17

Here, use the IFERROR function to return an error-free result when in the North, the Middle of the Fruit Paring Machine does not have an empty value.

5. Calculate the total cost

For example: Calculating the total cost of items SP1, SP2, SP3 based on the cost table and the number of sp code appearances in the product table sold.

In the cell to calculate enter the formula: SUM (SUMIFS (H5: H7, G5: G7, C5: C9)) .

Instructions and examples of array formulas Array Formulas in Excel Picture 18

After entering the formula, press Ctrl + Shift + Enter to get the result:

Instructions and examples of array formulas Array Formulas in Excel Picture 19

6. Calculate the total sales of the employees who sold the most

Here you can choose the sum of the n most sold employees with n = 2, 3, 4 .

For example, calculate the total sales of the 3 employees who sell the most.

In the cell to calculate, enter the formula: = SUM (LARGE (C6: C12, {1,2,3})) where {1,2,3} is an array containing the number of elements to sum, here is 3.

Instructions and examples of array formulas Array Formulas in Excel Picture 20

After entering the formula, press Ctrl + Shift + Enter to get the result:

Instructions and examples of array formulas Array Formulas in Excel Picture 21

For example: Calculate the total sales of the 4 employees with the largest sales by simply adding the 4 element to the array:

Instructions and examples of array formulas Array Formulas in Excel Picture 22

7. Use an array formula to number the order to ensure the data components are not deleted

Step 1: Highlight the data range you want to create ordinal numbers -> and above on entering the formula.

Instructions and examples of array formulas Array Formulas in Excel Picture 23

After entering the formula, press Ctrl + Shift + Enter to get the result:

Instructions and examples of array formulas Array Formulas in Excel Picture 24

By entering the ordinal number from the array formula to change the composition, the order of the column No. is not changed. For example, one more line: Boi black any line -> Right-click and choose Insert .

Instructions and examples of array formulas Array Formulas in Excel Picture 25

The row numbers 1, 2, 3, 4 and 5 are changed but the ordinal numbers in the ordinal column are not changed.

Instructions and examples of array formulas Array Formulas in Excel Picture 26

The above are some examples of how to use array formulas depending on your work of choice.

Good luck!