excel help please

If no one comes up with a function, you can try this:

PUT ALL THE NUMBERS IN COLUMN A
LEAVE TOP LINE FOR HEADERS
so numbers start at A2

in B2, put the following formula

=IF(A3=A2,"",A2)

Cut and past this down the entire column of B

It will list all the numbers that appear ONCE.

Then, in B1, put the following:

=COUNT(B2:B1000)

Note, "B1000" in formula should be the max of A2, so if data goes to A1500, formula is

=COUNT(B2:B1500)


Now, someone will post a one cell formula.....

thanks for your input shapeshifter

I've been looking at Help since I posted and I think Advanced filter is supposed to do it but i can't get it to work

so I'll try your method

It depends how you want your output.

Shapeshifters method is fine.  I think the one cell formula is:
=SUM(IF(FREQUENCY(A2:A1000,A2:A1000)>0,1))

You could also consider:
1 - Advanced filter: List range is your data range, check copy to another location and provide a range, then check unique records only.  This will give you a list of all unique values
2 - You could use pivotTable to do something similar, just put values in row headings and count of values in data

Try

Copy and paste this formula into a spare cell ~

=SUBTOTAL(2,AO2:AO10000) ~ that will count the list of unique values placed in column AO by the advanced filter

Now for the unique values in the advanced filter ~

Lets say all your numbers are in column B Starting at cell B2 ~ adjust if different.
Click on advanced filter,Click copy to another location,click unique records only,
List range box type B2:B10000, In the criteria range B2:B10000, in the copy to box type AO2

Easy in Access
Import excel spreadsheet as a table
Create query, selecting column containing numbers twice
Click on totals
Select count in second column selected
run query
Count rows

For table with a name of sheet1 with the column of numbers headed by number


SELECT Sheet1.Number, Count(Sheet1.Number) AS CountOfNumber
FROM Sheet1
GROUP BY Sheet1.Number;


I think that you can use queries in excel. Can you amend the above for excel?

Thanks everyone

sorted now

was it my PM that did it?

Mine was old skool

Stick it in a database (as data should be!) and do:

SELECT COUNT(DISTINCT([field])) from [table]