Excel Array Formulas Examples

This tutorial explains how to use array formulas in real-world data problems.

Array Formulas
Array formulas are confirmed by pressing Ctrl+Shift+Enter rather than simple ENTER.
Download Workbook

Example I

Suppose you are asked to calculate the sum of the products of column A and column B.
Array Formula Examples
=SUM((A3:A8)*(B3:B8)) is equivalent to [(A3*B3)+ (A4*B4)+....+ (A8*B8)]
Hit CTRL SHIFT ENTER to confirm =SUM((A3:A8)*(B3:B8)). If you hit it correctly, you would see the formula wrapped in curley brackets {}.
 Example II

Suppose you are asked to maximum value based on multiple criteria. The data are shown below -
Maximum Value based on Multiple Conditions
=MAX(IF((A3:A11="High")*(B3:B11="Low"),C3:C11))
Example III

Suppose you are asked to find out the top3 scorers in a class given the students can have same score.
Array Formulas Examples
=INDEX($A$3:$A$9,MATCH(LARGE($B$3:$B$9-ROW($B$3:$B$9)/10^5,ROWS(A$1:A1)),$B$3:$B$9-ROW($B$3:$B$9)/10^5,0))

1. INDEX- MATCH looks for a value in the right column of a table and returns a value in the same row from a column you specify.

2. MATCH always return the first occurrence so we first need to create unique values in range.
$B$3:$B$9-ROW($B$3:$B$9)/10^5
See how the above formula evaluates -
B3 - ROW(B3)/10^5 = 77-(3/100000)
B4 - ROW(B4)/10^5 = 95-(4/100000)
.
.
B9 - ROW(B9)/10^5 = 85-(9/100000)

It gives us unique values -
(76.99997, 94.99996,88.99995,52.99994,94.99993,48.99992,84.99991)

Example IV

We need to extract UNIQUE names from the list.
Extract Unique List
Enter the following formula in cell C3
=INDEX($A$3:$A$9,MATCH(0,COUNTIF($C$2:$C2,$A$3:$A$9),0))

Example V : Sum of Digits
Sum of Digits
=SUM(MID(B3,ROW(INDIRECT("1:" & LEN(B3))),1)*1)
 The INDIRECT function returns a set of text values, in this case the values 1 through number of digits in a cell. The ROW function in turn generates a  multi-cell column array.
ROW(INDIRECT("1:" & LEN(B3)) evaluates to ROW(1:5) as the number of digits in cell B3 is 5.

Example VI : Count Uppercase and lowercase letters
Count Uppercase and Lowercase Letters
Count Uppercase letters
=SUMPRODUCT(LEN(A3)-LEN(SUBSTITUTE(A3,CHAR(ROW(INDIRECT("65:90"))),""))) 
CHAR(ROW(INDIRECT("65:90"))) generates capital A through Z.

Count Lowercase letters
=SUMPRODUCT(LEN(A3)-LEN(SUBSTITUTE(A3,CHAR(ROW(INDIRECT("97:122"))),""))) 
CHAR(ROW(INDIRECT("97:122"))) generates lowercase a through z.

Download Workbook

Excel Tutorials : 100 Excel Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

4 Responses to "Excel Array Formulas Examples"

  1. Nice post. Never tried going into array formulas but this helped a lot to understand the basics of it.

    ReplyDelete
    Replies
    1. Thank you for stopping by my blog. Cheers!

      Delete
  2. thanks my name amarnathverma@outlook.com

    ReplyDelete
  3. Thanks. Know I have got the better undersetting of Array.

    ReplyDelete

Next → ← Prev