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...


--
mallets123
------------------------------------------------------------------------
mallets123's Profile: http://www.excelforum.com/member.php...o&userid=25090
View this thread: http://www.excelforum.com/showthread...hreadid=386023

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

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

www.OfficeArticles.com
www.MyExpertsOnline.com


"mallets123" wrote
in message ...

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...


--
mallets123
------------------------------------------------------------------------
mallets123's Profile:
http://www.excelforum.com/member.php...o&userid=25090
View this thread: http://www.excelforum.com/showthread...hreadid=386023

Some options...

[A]

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

[b]

=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.

mallets123 wrote:
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.

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

Basically my example is I have a groups that are traveling to different
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 the
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:F 8,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 keep
a cumulative total of different states visited, isn't it correct to list
the states seperately somewhere where excel can count the total of both
groups?

Please help...


--
mallets123
------------------------------------------------------------------------
mallets123's Profile: http://www.excelforum.com/member.php...o&userid=25090
View this thread: http://www.excelforum.com/showthread...hreadid=386023