counting diffeneces in range

[mallets123]

Is there a function which will count in a range of cells (text, i.e.
state abbreviations) each time a new state abbreviation occurs?

Thanks for any help with this, going crazy here...

 

[Anne Troy]

You want Unique records, yes?
You can try this:
http://www.officearticles.com/excel/count_unique_values_in_microsoft_excel.htm

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[Aladin Akyurek]

Some options...

[A]

=SUMPRODUCT((Range<>"")/COUNTIF(Range,Range&""))



=SUM(IF(Range<>"",1/(COUNTIF(Range,Range)))

which must be confirmed with control+shift+enter.

[C]

=COUNTDIFF(Range,,"")

which requires Longre's morefunc.xll add-in.

Is there a function which will count in a range of cells (text, i.e.
state abbreviations) each time a new state abbreviation occurs?

Thanks for any help with this, going crazy here...

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[mallets123]

Thanks for all of your help, I've got it to work like I want it, howeve
I now need it to list these state abbreviations that are different in
series of cells.

Basically my example is I have a groups that are traveling to differen
states. I've got excel counting how many different states were visited
Now I need excel to list them in a series of cells so I can count th
total number of different states visited between several groups
Whoah....

I'm using

=SUM(IF(FREQUENCY(IF(LEN(F5:F8)>0,MATCH(F5:F8,F5:F8,0),""),IF(LEN(F5:F8)>0,MATCH(F5:F8,F5:F8,0),""))>0,1))

this counts the different states incountered.

If two groups are traveling in different time frames but I need to kee
a cumulative total of different states visited, isn't it correct to lis
the states seperately somewhere where excel can count the total of bot
groups?

Please help..