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/...soft_excel.htm

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

www.OfficeArticles.com
www.MyExpertsOnline.com


"mallets123" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[mallets123]

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

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Aladin Akyurek]

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.

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.

[Anne Troy]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Aladin Akyurek]

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.