Microsoft Office Tutorials and References
In Depth Information
GET AN ARRAY OF UNIQUE VALUES FROM A LIST
Alternate Strategy: If you replace SUM in the original formula with SUMPRODUCT
and replace the multiplication sign * with a comma, you could enter the formula
as:
=SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},""))),{1,2,3,4,5,6,7,8,9})
This form would probably look a bit more intuitive to some users.
Illustrative Examples:
Text
Result
76432
22
*****(8,121)
12
76*432
22
764 test 32
22
1 test 2
3
156.546
27
3127543.791
44
t=18317; p=239317
45
24 / 12
9
30°54’43”
19
SSN 421-89-7322
38
800/555-1212
29
3.142
PI() displayed to 14 decimal points
77
06:00 PM
Underlying value = 0.75
12
Summary: You can use SUM (or SUMPRODUCT ) to build a formula that returns
the sum of all the digits in a string of text.
GET AN ARRAY OF UNIQUE VALUES
FROM A LIST
Challenge: You want to extract all unique values from a column of text data
that may contain several instances of a particular value. A procedure like this is
useful when you need to populate a list box or combo box with unique values
for user selection.
Solution: Assume that your spreadsheet contains a list of names in the range
A2:A30 on Sheet1. Cell A1 contains the header Name.
You can manually solve this problem by selecting Data, Filter, Advanced Filter
dialog.

Search JabSto ::

Custom Search