Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
pjr pjr is offline
external usenet poster
 
Posts: 10
Default How can I count how many times different texts repeat in a column?

Hey,

I have a column filled with text. there are about 100 cells in this column.
Those cells each have the name of a single corporation within them. many of
those corporation names are repeated throughout the column, but not all of
them.

How can I count the total # of corporation names present in that column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default How can I count how many times different texts repeat in a column?

Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"pjr" wrote in message
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them. many
of
those corporation names are repeated throughout the column, but not all of
them.

How can I count the total # of corporation names present in that column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.



  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,572
Default How can I count how many times different texts repeat in a column?

Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"pjr" wrote in message
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them. many
of
those corporation names are repeated throughout the column, but not all of
them.

How can I count the total # of corporation names present in that column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.



  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1,533
Default How can I count how many times different texts repeat in a column?

Hi

Select the column "Data" "Filter" "Advanced Filter" Check "Unique
records only" OK

In the bottom of the screen you will see the number of Unique records.

To show all records again Data Filter Show All

Regards,
Per

"pjr" skrev i meddelelsen
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them. many
of
those corporation names are repeated throughout the column, but not all of
them.

How can I count the total # of corporation names present in that column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.


  #5   Report Post  
Posted to microsoft.public.excel.newusers
pjr pjr is offline
external usenet poster
 
Posts: 10
Default How can I count how many times different texts repeat in a col

When I copied this over to the cell, I was told t hat there were not enough
arguments in the function

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"pjr" wrote in message
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them. many
of
those corporation names are repeated throughout the column, but not all of
them.

How can I count the total # of corporation names present in that column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.






  #6   Report Post  
Posted to microsoft.public.excel.newusers
pjr pjr is offline
external usenet poster
 
Posts: 10
Default How can I count how many times different texts repeat in a col

This did exactly what I'm looking to do, but I need it to be done in a cell,
as a function, on a separate worksheet.

"Per Jessen" wrote:

Hi

Select the column "Data" "Filter" "Advanced Filter" Check "Unique
records only" OK

In the bottom of the screen you will see the number of Unique records.

To show all records again Data Filter Show All

Regards,
Per

"pjr" skrev i meddelelsen
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them. many
of
those corporation names are repeated throughout the column, but not all of
them.

How can I count the total # of corporation names present in that column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.



  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default How can I count how many times different texts repeat in a col

In that case, you have mis-copied Biff's formula.

Are you missing the comma or does your system require a semi-colon, not a comma?


Gord Dibben MS Excel MVP

On Tue, 15 Apr 2008 08:48:01 -0700, pjr wrote:

When I copied this over to the cell, I was told t hat there were not enough
arguments in the function

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"pjr" wrote in message
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them. many
of
those corporation names are repeated throughout the column, but not all of
them.

How can I count the total # of corporation names present in that column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.





  #8   Report Post  
Posted to microsoft.public.excel.newusers
pjr pjr is offline
external usenet poster
 
Posts: 10
Default How can I count how many times different texts repeat in a col

you are right. I got it to work now. Thank you very much. One more thing,
though:

I need to incorporate a couple of criteria into this formula. In a separate
column is written whether or not these corporations are Non Profit or For
Profit. And again in another is listed what program they are applying for.
The non profit or for profit status is listed as text, but the program
listings are shown as numerical requests in seperate columns (i.e. if they
are applying for SAIL, then in the Sail column is a $ amount; for HOME, then
in the HOME column there is a $ amount...). How do I build on this formula to
specify how many For Profit corporations there are? and then how do I specify
how many For Profit corporations there are applying for each program?

Thank you for the help,
pjr

"Gord Dibben" wrote:

In that case, you have mis-copied Biff's formula.

Are you missing the comma or does your system require a semi-colon, not a comma?


Gord Dibben MS Excel MVP

On Tue, 15 Apr 2008 08:48:01 -0700, pjr wrote:

When I copied this over to the cell, I was told t hat there were not enough
arguments in the function

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"pjr" wrote in message
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them. many
of
those corporation names are repeated throughout the column, but not all of
them.

How can I count the total # of corporation names present in that column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.





  #9   Report Post  
Posted to microsoft.public.excel.newusers
jai jai is offline
external usenet poster
 
Posts: 71
Default How can I count how many times different texts repeat in a col

Hi. I had the same problemas pjr. Tried This formula and it worked for a
small subset of my data. When I try it on the full 70 000 entries my computer
locks up. Is there a more efficient way of doing this? Cheers, Jai

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"pjr" wrote in message
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them. many
of
those corporation names are repeated throughout the column, but not all of
them.

How can I count the total # of corporation names present in that column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.




  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default How can I count how many times different texts repeat in a col

If you can download and install the Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

There is a function, COUNTDIFF, that is significantly faster than the
standard:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

I haven't tested it yet in Excel 2007 on really big ranges, 65,000 rows,
but in my (working) version of Excel (2002) it calculates 65,000 rows
without a blip!


--
Biff
Microsoft Excel MVP


"Jai" wrote in message
...
Hi. I had the same problemas pjr. Tried This formula and it worked for a
small subset of my data. When I try it on the full 70 000 entries my
computer
locks up. Is there a more efficient way of doing this? Cheers, Jai

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"pjr" wrote in message
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them.
many
of
those corporation names are repeated throughout the column, but not all
of
them.

How can I count the total # of corporation names present in that
column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.








  #11   Report Post  
Posted to microsoft.public.excel.newusers
jai jai is offline
external usenet poster
 
Posts: 71
Default How can I count how many times different texts repeat in a col

Thanks

"T. Valko" wrote:

If you can download and install the Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

There is a function, COUNTDIFF, that is significantly faster than the
standard:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

I haven't tested it yet in Excel 2007 on really big ranges, 65,000 rows,
but in my (working) version of Excel (2002) it calculates 65,000 rows
without a blip!


--
Biff
Microsoft Excel MVP


"Jai" wrote in message
...
Hi. I had the same problemas pjr. Tried This formula and it worked for a
small subset of my data. When I try it on the full 70 000 entries my
computer
locks up. Is there a more efficient way of doing this? Cheers, Jai

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"pjr" wrote in message
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them.
many
of
those corporation names are repeated throughout the column, but not all
of
them.

How can I count the total # of corporation names present in that
column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.






  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default How can I count how many times different texts repeat in a col

You're welcome!

--
Biff
Microsoft Excel MVP


"Jai" wrote in message
...
Thanks

"T. Valko" wrote:

If you can download and install the Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

There is a function, COUNTDIFF, that is significantly faster than the
standard:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

I haven't tested it yet in Excel 2007 on really big ranges, 65,000 rows,
but in my (working) version of Excel (2002) it calculates 65,000 rows
without a blip!


--
Biff
Microsoft Excel MVP


"Jai" wrote in message
...
Hi. I had the same problemas pjr. Tried This formula and it worked for
a
small subset of my data. When I try it on the full 70 000 entries my
computer
locks up. Is there a more efficient way of doing this? Cheers, Jai

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"pjr" wrote in message
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them.
many
of
those corporation names are repeated throughout the column, but not
all
of
them.

How can I count the total # of corporation names present in that
column?
i.e. if Vestcor repeats 20 times, it is only counted once; if
Landmark
repeats zero times it also is counted once... etc.








  #13   Report Post  
Posted to microsoft.public.excel.newusers
jai jai is offline
external usenet poster
 
Posts: 71
Default How can I count how many times different texts repeat in a col

I download and installed the add-in. The COUNTDIFF function works for 2003
but doesn't seem to be in 2007 at all. I'm looking under the formula tab as
well as typing =COUNTDIFF into the formula bar with no success. Am I just
missing something obvious here? Thanks for your help so far.

"T. Valko" wrote:

If you can download and install the Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

There is a function, COUNTDIFF, that is significantly faster than the
standard:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

I haven't tested it yet in Excel 2007 on really big ranges, 65,000 rows,
but in my (working) version of Excel (2002) it calculates 65,000 rows
without a blip!


--
Biff
Microsoft Excel MVP


"Jai" wrote in message
...
Hi. I had the same problemas pjr. Tried This formula and it worked for a
small subset of my data. When I try it on the full 70 000 entries my
computer
locks up. Is there a more efficient way of doing this? Cheers, Jai

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"pjr" wrote in message
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them.
many
of
those corporation names are repeated throughout the column, but not all
of
them.

How can I count the total # of corporation names present in that
column?
i.e. if Vestcor repeats 20 times, it is only counted once; if Landmark
repeats zero times it also is counted once... etc.






  #14   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default How can I count how many times different texts repeat in a col

Are you sure the add-in is installed in Excel 2007?

If you goto the Formulas tab there should be a group called Morefunc. This
is the eqivalent of the Insert Function button on the formula bar.

You can also use the Insert Function button on the formula bar. From the
category drop down select Morefunc.

If you type in the formula and got a #NAME? error then that means the add-in
isn't installed.

--
Biff
Microsoft Excel MVP


"Jai" wrote in message
...
I download and installed the add-in. The COUNTDIFF function works for 2003
but doesn't seem to be in 2007 at all. I'm looking under the formula tab
as
well as typing =COUNTDIFF into the formula bar with no success. Am I just
missing something obvious here? Thanks for your help so far.

"T. Valko" wrote:

If you can download and install the Morefunc.xll add-in from:

http://xcell05.free.fr/morefunc/english/index.htm

There is a function, COUNTDIFF, that is significantly faster than the
standard:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

I haven't tested it yet in Excel 2007 on really big ranges, 65,000 rows,
but in my (working) version of Excel (2002) it calculates 65,000 rows
without a blip!


--
Biff
Microsoft Excel MVP


"Jai" wrote in message
...
Hi. I had the same problemas pjr. Tried This formula and it worked for
a
small subset of my data. When I try it on the full 70 000 entries my
computer
locks up. Is there a more efficient way of doing this? Cheers, Jai

"T. Valko" wrote:

Try this:

=SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&""))

--
Biff
Microsoft Excel MVP


"pjr" wrote in message
...
Hey,

I have a column filled with text. there are about 100 cells in this
column.
Those cells each have the name of a single corporation within them.
many
of
those corporation names are repeated throughout the column, but not
all
of
them.

How can I count the total # of corporation names present in that
column?
i.e. if Vestcor repeats 20 times, it is only counted once; if
Landmark
repeats zero times it also is counted once... etc.








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count how many times the same word appears in column Alexa Excel Discussion (Misc queries) 6 April 9th 23 12:51 PM
Count the number of times a name occurs in a column Gary Excel Worksheet Functions 2 December 28th 06 01:07 AM
How to count the number of times a string appears in a column? btsray Excel Worksheet Functions 0 January 25th 06 11:15 PM
How do I count how many times a code appears in a column? Leo Excel Worksheet Functions 3 June 2nd 05 08:50 PM
How do I count how many times x appears in a column? Ian Roberts Excel Worksheet Functions 2 November 7th 04 04:13 PM


All times are GMT +1. The time now is 10:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"