How can I count how many times different texts repeat in a column?

[pjr]

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.

 

[T. Valko]

Try this:

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

 

[RagDyer]

Try this:

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

 

[Per Jessen]

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]

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

 

[pjr]

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.

 

[Gord Dibben]

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

 

[pjr]

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

 

[Jai]

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]

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!

 

[Jai]

Thanks

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!

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks

 

[Jai]

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]

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.