In Excel 2003, I have a large list (database). In one of the fields I have
multiple company names. Each company may be listed many times. Is there a
way to count the number of different companies that are in that field? I
don't care how many times any one company appears, just how many different
companies there are.

Loris,

=COUNTIF(RANGE,"COMPANY NAME") where "RANGE" contains the list of company
names should give you what you want.

"Loris" wrote:

> In Excel 2003, I have a large list (database). In one of the fields I have
> multiple company names. Each company may be listed many times. Is there a
> way to count the number of different companies that are in that field? I
> don't care how many times any one company appears, just how many different
> companies there are.

Try this:

If there might be empty cells and they are to be excluded:

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

If there will not be empty cells:

=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))

Note that these are slow to calculate on large amounts of data (1000's of
rows).

--
Biff
Microsoft Excel MVP


"Loris" <(E-Mail Removed)> wrote in message
news:E2E46A97-2163-4926-9A62-(E-Mail Removed)...
> In Excel 2003, I have a large list (database). In one of the fields I have
> multiple company names. Each company may be listed many times. Is there
> a
> way to count the number of different companies that are in that field? I
> don't care how many times any one company appears, just how many different
> companies there are.

Loris,

I apologize for my earlier response as it was incorrect; I misread your
request.

GMc

"Loris" wrote:

> In Excel 2003, I have a large list (database). In one of the fields I have
> multiple company names. Each company may be listed many times. Is there a
> way to count the number of different companies that are in that field? I
> don't care how many times any one company appears, just how many different
> companies there are.

Don't know if it will speed up anything, but you can replace
SUMPRODUCT with SUM in these two formulas.

Lars-Åke

On Sat, 29 Nov 2008 14:31:45 -0500, "T. Valko"
<(E-Mail Removed)> wrote:

>Try this:
>
>If there might be empty cells and they are to be excluded:
>
>=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
>
>If there will not be empty cells:
>
>=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
>
>Note that these are slow to calculate on large amounts of data (1000's of
>rows).

This is exactly what I needed. I wasn't familiar with the SUMPRODUCT
function. Now I have to figure out what it is doing so I can understand why
it works. Thanks much.

"T. Valko" wrote:

> Try this:
>
> If there might be empty cells and they are to be excluded:
>
> =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
>
> If there will not be empty cells:
>
> =SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
>
> Note that these are slow to calculate on large amounts of data (1000's of
> rows).
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Loris" <(E-Mail Removed)> wrote in message
> news:E2E46A97-2163-4926-9A62-(E-Mail Removed)...
> > In Excel 2003, I have a large list (database). In one of the fields I have
> > multiple company names. Each company may be listed many times. Is there
> > a
> > way to count the number of different companies that are in that field? I
> > don't care how many times any one company appears, just how many different
> > companies there are.
>
>
>

Hi,

1. Gary - Your idea can be modified as follow, provided there are no spaces:

=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))

2. Antares - to understand the formula select a portion of the formula on
the formula bar, such as COUNTIF(A1:A10,A1:A10) and press the F9 key.
Examine the results and press Esc. test another complete piece...

If this helps, please click the Yes button

Cheers,
Shane Devenshire



"Gary Mc" wrote:

> Loris,
>
> I apologize for my earlier response as it was incorrect; I misread your
> request.
>
> GMc
>
> "Loris" wrote:
>
> > In Excel 2003, I have a large list (database). In one of the fields I have
> > multiple company names. Each company may be listed many times. Is there a
> > way to count the number of different companies that are in that field? I
> > don't care how many times any one company appears, just how many different
> > companies there are.

Hi,

If you know there are going to be blanks in your range then you can use:
=SUMPRODUCT(1/COUNTIF(A3:A19,A3:A19&""))-1
if there won't be any blanks for sure then
=SUMPRODUCT(1/COUNTIF(A3:A19,A3:A19))
but if you don't know it's better to use the previously suggested formula.

If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Loris" wrote:

> In Excel 2003, I have a large list (database). In one of the fields I have
> multiple company names. Each company may be listed many times. Is there a
> way to count the number of different companies that are in that field? I
> don't care how many times any one company appears, just how many different
> companies there are.

Hi Lars,

SUMPRODUCT is faster than SUM array entered. It might only be 15-25% faster
but its something.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Lars-Ã…ke Aspelin" wrote:

> Don't know if it will speed up anything, but you can replace
> SUMPRODUCT with SUM in these two formulas.
>
> Lars-Ã…ke
>
> On Sat, 29 Nov 2008 14:31:45 -0500, "T. Valko"
> <(E-Mail Removed)> wrote:
>
> >Try this:
> >
> >If there might be empty cells and they are to be excluded:
> >
> >=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
> >
> >If there will not be empty cells:
> >
> >=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
> >
> >Note that these are slow to calculate on large amounts of data (1000's of
> >rows).
>
>

In this application the SUM array is marginally faster than SUMPRODUCT.

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

Average calc time (5 calculations): 0.063036 seconds

=SUM((A1:A1000<>"")/COUNTIF(A1:A1000,A1:A1000&""))

Average calc time (5 calculations): 0.062782 seconds

=SUMPRODUCT(1/COUNTIF(A1:A1000,A1:A1000))

Average calc time (5 calculations): 0.31791 seconds

=SUM(1/COUNTIF(A1:A1000,A1:A1000))

Average calc time (5 calculations): 0.316408 seconds

Calc times measured using Charles Williams' RangeTimer method.

--
Biff
Microsoft Excel MVP


"Lars-Åke Aspelin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Don't know if it will speed up anything, but you can replace
> SUMPRODUCT with SUM in these two formulas.
>
> Lars-Åke
>
> On Sat, 29 Nov 2008 14:31:45 -0500, "T. Valko"
> <(E-Mail Removed)> wrote:
>
>>Try this:
>>
>>If there might be empty cells and they are to be excluded:
>>
>>=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
>>
>>If there will not be empty cells:
>>
>>=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
>>
>>Note that these are slow to calculate on large amounts of data (1000's of
>>rows).
>