Single-cell array formula examples
Note: All of the names in this workbook are sheet-level names.
Summing a range that contains error values
Item Cost Number Total
A-933 10 4 40
A-833 6 4 24
C-902 #N/A 3 #N/A
F-902 10 #VALUE!
J-111 3 12 36
R-234 20 6 120
S-984 5 4 20
TOTAL: #N/A
240 <-- SUM, excluding errors
240 <-- Compatible with pre-Excel 2007
240 <-- Uses AGGREGATE function
re-Excel 2007
Counting error values in a range
0 12 #DIV/0! #N/A
1 13 13 #NAME?
2 14 7 2
3 15 5 3
4 16 4 4
5 17 3.4 5
6 18 3 6
7 19 2.714286 7
8 20 2.5 8
9 21 Err:502 9
10 22 2.2 10
11 23 2.090909 11
Number of error values: 4
Conditional summing
-3 Sum greater than 0 AND less than or equal to 5: 15
-2 Sum less than 0 OR greater than 5: 34
-1
0
1
2
3
4
5
6
7
8
9
10
Summing the n largest values
5 9 13
15 19 23
15 19 23
17 21 25
19 23 27
20 24 28
22 26 30
24 28 32
35 39 43
37 41 45
43 47 51
100 101 102
Number to sum: 3
Sum: #REF!
Exclude zero from average
Sales Person Sales
Abner 23,991
Baker 15,092
Charleston 0
Davis 11,893
Ellerman 32,116
Flugelhart 29,089
Gallaway 0
Harrison 33,211
Average with zeros: 18,174
Average without zeros (array formula): 24,232
Uses AVERAGEIF (no array formula): 24,232
Is a value contained in a range?
Enter a Name: Donald Found
Al Daniel Harold Lyle Richard
Allen Dave Ian Maggie Rick
Andrew David Jack Margaret Robert
Anthony Dennis James Marilyn Rod
Arthur Don Jan Mark Roger
Barbara Donald Jef Marvin Ronald
Bernard Doug Jefrey Mary Russ
Beth Douglas Jerry Matt Sandra
Bill Ed Jim Mel Scott
Bob Edward Joe Merle Simon
Brian Eric John Michael Stacy
Bruce Fran Joseph Michelle Stephen
Cark Frank Kathy Mike Steven
Carl Fred Kathy Norman Stuart
Charles Gary Keith Patrick Susan
Chris George Kenneth Paul Terry
Chuck Glenn Kevin Peter Thomas
Clark Gordon Larry Phillip Timothy
Curt Greg Leonard Ray Vincent
Dan Gregory Louise Rebecca Wendy
Non-array formula: Donald Found
Counting the number of differences between two ranges
MyData YourData
1 34 1 34
3 35 3 35
5 36 5 36
7 37 7 38
9 38 9 38
11 39 11 39
13 40 13 40
15 41 14 41
17 42 17 42
19 43 19 43
21 44 21 44
23 45 23 43
25 46 25 46
27 47 27 47
29 48 29 48
31 49 31 49
33 50 33 50
Differences found: 3
Alternate version: 3
0 0 0 0
0 0 0 0
0 0 0 0
0 1 0 1
0 0 0 0
0 0 0 0
0 0 0 0
1 0 1 0
0 0 0 0
0 0 0 0
0 0 0 0
0 1 0 1
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
Location of the maximum value in a range
1 Maximum value: 78
3 Row of maximum value: 15
5 Address of maximum value: $A$15
6
9
90
12
80
15
22 70
32 60
44 50
32
40
55
30
78
49 20
41 10
0
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
The row of the nth occurrence of a value
1 Value to find: 1
3 Occurrence of Value: 3
3 It's in row: 17
6
6
6
8
8
1
8
8
8
8
1
1
Longest text in a range
January
February Longest text: September
March
April
May
June
July
August
September
October
November
December
Valid items in a range
Master MyList
AZ-101 AZ-109 All items valid? 0
AZ-102 AZ-105 Invalid items: 2
AZ-103 AZ-109 First invalid item: AZ-121
AZ-104 AZ-107
AZ-105 AZ-121
AZ-106 AZ-122
AZ-107
AZ-108
AZ-109
AZ-110
AZ-111
AZ-112
AZ-113
AZ-114
AZ-115
AZ-116
Sum of the digits of a value
Improved
Number Sum of Digits
Version
132 #REF! 0
9 #REF! 0
111111 #REF! 0
980991 #REF! 0
-980991 #REF! 0
409 #REF! 0
123A6 #REF! 0
12 #REF! 0
98,763,023 #REF! 0
111,111,111 #REF! 0
Summing rounded values
Description Quantity Unit Price Discount Total
Widgets 6 $11.69 5.23% $66.47
Sprockets 8 $9.74 5.23% $73.84
Snapholytes 3 $9.85 5.23% $28.00
GRAND TOTAL $168.32 <-- appears to be incorre
Sum of rounded values: $168.31
<-- appears to be incorrect
Sheet14
Summing every nth value
Data
1 3 =nth
2 #REF! = Result
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Page 18
Removing non-numeric characters from a cell
123RX-T Err:504
XT-656 Err:504
666T Err:504
T1093 Err:504
5T5 Err:504
Determining the closest value in a range
-12 Target Value: 45
203 Closest Match: 48
566
12
20
21
40
48
56
72
102
109
96
97
105
137
1234
165
7
Returning the last value in a column or row
Array Non-Array
Last non-empty cell in Column A: 101 101
Last non-empty cell in Row 16: Err:508 45
5 1
23 2
43 3
2 4
5
2 6
2 7
54 8
9
101 10 45
