Distinct Count in EXCEL 2

[FunkMaster]

Hello
i wonder if anyone can help me? being an bit of an idiot but is there an way to get an distinct count of an colmun of data? the data is text.

cheers

5% is not an rise, its an poke in the eye.

 

[GlennUK]

One way to find the number of distinct values in a range is to use an array formula like this ....

=SUM(1/COUNTIF(A1:A13,A1:A13))

but entered using CTRL-SHIFT-ENTER instead of ENTER. ( this will put curly brackets around the formula, to indicate an array formula )

Cheers, Glenn.

 

[FunkMaster]

Cool cheers thanks for the fast respone , yes it works, but can i ask why? what does the curly brakets do?

now i just got to do this 2640 times, i wonder if i can vb it. cheers still

cheers

wayne 5% is not an rise, its an poke in the eye.

 

[xlbo]

I would advise against 2640 array formulae in your spreadsheet - it'll go sloooooooow

Suggest using a pivot table:

Select your single column of text. Make sure it has a header (lets call it myHead)

Goto Data>Pivot Table
Step thru the wizard till you get to the pivot table arrangement screen
Drag and drop "myHead" into the ROW field
then, drag and drop "myHead" into the DATA area
Double click on it in there and check that it says COUNT of myHead
Decide where you want the pivot table to be and then just count the rows

HTH Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[FunkMaster]

Hi xlbo

ahhh soz, yes i have to do it on 66 spreadsheets in one workbook , and theres 40 workbooks, the count is only for one distinct row of data. and so only need one count per spreadsheet, just working out how to vb it now.

cheers though

wayne 5% is not an rise, its an poke in the eye.

 

[xlbo]

Use the FORMULAARRAY property of the range object - it'll automatically put the curly brackets on
eg
sheets(i).range("Z1&quot.formulaarray = "formula goes here"
Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[FunkMaster]

cheers that helps a lot as well, my office assitant just kept telling me it was time for lunch.

cheers

wayne 5% is not an rise, its an poke in the eye.