ttrbrtsn

asked on

# how to figure out about many different numbers in a column

i have a spreadsheet. and in the spreadsheet I have a column with 22,000 line items. (1 - 22,000). however, when I highlight the column, I can see the "count", sum", etc...

I want to know is how many different numbers are there in this column. I can see many duplicate numbers (for billing purposes). there are just too many lines items to do this manually

. is there a formula I can use ?

thank you

I want to know is how many different numbers are there in this column. I can see many duplicate numbers (for billing purposes). there are just too many lines items to do this manually

. is there a formula I can use ?

thank you

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
Sort your column with numbers eg.Col-A

Type this formula on the first row =(A1<>A2)*1 and copy that to the end of the data row 22.000.

Nou Sum this column and you have the number of diferences :-)

Type this formula on the first row =(A1<>A2)*1 and copy that to the end of the data row 22.000.

Nou Sum this column and you have the number of diferences :-)

If data is in A1:A22000 try this formula in an adjacent column

=SUMPRODUCT((A1:A22000<>"")/COUNTIF(A1:A22000,A1:A22000&""))

That will count the number of different values in the range while ignoring (and not counting) and blanks

regards, barry

=SUMPRODUCT((A1:A22000<>""

That will count the number of different values in the range while ignoring (and not counting) and blanks

regards, barry

ASKER

im sure the others worked. however I found this to be the easiest

http://www.get-digital-help.com/2009/06/09/count-unique-values-in-a-column-in-excel/