how to count different cells

[scottlarock]

Hello,
I am trying to find a formula that would count the number of different cells in column B from cells B4 to B500.

I would like to indicate the number of different cells in cells A4 to A500 (i.e: from 1 to ...200 if there are 200).

The goal is to count the number of projects, and the projects are repeated several times in column B.

Tons of thanks in advance for your help !!

Phil

 

[Neofish]

Firstly, I want to check that I understand your question.
You want to find how many distinct values are in a range of cells. I know how to do this manually (using VB) but you may be able to use several functions together.
Something that could help is frequency calculation (ie used in cumulative frequencies and other stuff). All you would need to do is get the number of frequencies collected by the function (and ignore the frequencies themselves for now).
Otherwise I'd write it like this:
Create something that you can do an IsIn() style content check on (ie string, array, db, whatever, I'm not so experienced with VB collections yet)
Iterate the range
If the cell doesn't exist, add it to the list

Then get the list size

Hope this helped, I will return if needed.
NF

 

[Dannyh1]

Hi Try:


=SUM(IF(FREQUENCY(IF(LEN(B4:B500)>0,MATCH(B4:B500,B4:B500,0),""), IF(LEN(B4:B500)>0,MATCH(B4:B500,B4:B500,0),""))>0,1))

Enter with Shift-Cntrl-Enter so it looks like

{=SUM(IF(FREQUENCY(IF(LEN(B4:B500)>0,MATCH(B4:B500,B4:B500,0),""), IF(LEN(B4:B500)>0,MATCH(B4:B500,B4:B500,0),""))>0,1))}