Excel: Need a formula which can obtain a total amount of unique values in a range

Example: A10=111, A11=101, A12=111, A13=4, A14=101, A15=blank. The total is 3 unique values. I need a dynamic formul开发者_如何学Goa within a cell that can calculate all the unique values. Blanks will be in the set range and should not be included in the total. Thanks.

From Microsoft:

Count the number of unique text and number values in cells B2:B10 (which must not contain blank cells) (7)

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

http://office.microsoft.com/en-us/excel-help/count-occurrences-of-values-or-unique-values-in-a-data-range-HP003056118.aspx

This can be done in VBA: http://www.google.com/search?q=VBA+distinct

If you only need to do this one time, you can do it semi-manually in a couple steps:

1) Sort the values (say, in B10-B15)

2) In the next column, use this formula in each cell: =IF(C10<>B9,C10,""). (Only show the value if it is not equal to the one above it.) That will give you the unique values.

By "total amount" do you mean a sum or count of distinct values? Either way, you can do that with the calculated column's values.

You can Utilize this VBA code, The codes are well commented for you to understand.

Sub sample_CntDist()
'
' Counting Distinct numbers, not counting blank values presenting count distinct in a cell Macro
'
'
Columns("A:A").Select 'select the column which have numbers
Selection.Copy 'copy the selection
Sheets("Sheet2").Select 'select a new sheet
Range("A1").Select 'select first cell
ActiveSheet.Paste 'paste the copied data
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A$18").RemoveDuplicates Columns:=1, Header:=xlNo 'remove duplicates
Range("B1").Select 'select cell b1
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],""<>"")" 'count distinct number excluding blanks
Sheets("Sheet1").Select 'select original sheet
Range("C2").Select 'select cell c2
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "count distinct " 'give a caption for the result
Range("D2").Select 'select cell d2
Sheets("Sheet2").Select 'select the temp sheet
Selection.Copy 'copy the distinct count
Sheets("Sheet1").Select 'select and paste the result as values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Visible = False 'hide temp sheet
End Sub