I have a list of companies which occur more than once in a column. I want to
be able to count how many different companies there are, not how many times
they occur. Any help, please.

Thanks

You can use Advanced filter options and select 'Unique records only', it will
list the unique records from the list. You can also build a pivot, but this
is just too time consuming.

Regards,
Pranav Vaidya

"Nigel" wrote:

I have a list of companies which occur more than once in a column. I want to
be able to count how many different companies there are, not how many times
they occur. Any help, please.

Thanks

Hi Pranav

Thanks for your reply, but I need to have a count of how many.

"Pranav Vaidya" wrote:

You can use Advanced filter options and select 'Unique records only', it will
list the unique records from the list. You can also build a pivot, but this
is just too time consuming.

Regards,
Pranav Vaidya

"Nigel" wrote:

I have a list of companies which occur more than once in a column. I want to
be able to count how many different companies there are, not how many times
they occur. Any help, please.

Thanks

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nigel" wrote in message
...
I have a list of companies which occur more than once in a column. I want
to
be able to count how many different companies there are, not how many
times
they occur. Any help, please.

Thanks

Hi Bob
Just what the doctor ordered, works a treat, many thanks. As an aside, what
do the 2 hyphens do, as it seems to work with or without them?

"Bob Phillips" wrote:

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nigel" wrote in message
...
I have a list of companies which occur more than once in a column. I want
to
be able to count how many different companies there are, not how many
times
they occur. Any help, please.

Thanks

You are right they are not necessary in this case. It is used when doing
conditional tests, such as (A1:A10="Apple") to coerce an array of TRUE/FALSE
to an array of 1/0, which is used in SUMPRODUCT multi-conditional tests.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nigel" wrote in message
...
Hi Bob
Just what the doctor ordered, works a treat, many thanks. As an aside,
what
do the 2 hyphens do, as it seems to work with or without them?

"Bob Phillips" wrote:

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Nigel" wrote in message
...
I have a list of companies which occur more than once in a column. I
want
to
be able to count how many different companies there are, not how many
times
they occur. Any help, please.

Thanks

Bob

This answer/examnple also works very well for my problem. (Thanks you)
However, as my databases (7 in total) are some 50,000 lines long and I have
to do 2 calculations per database this takes a considerable amount of time.
Is there a quicker solution ? (apart from buying a PC with more memory /
processing space etc.)

Bob

"Bob Phillips" wrote:

You are right they are not necessary in this case. It is used when doing
conditional tests, such as (A1:A10="Apple") to coerce an array of TRUE/FALSE
to an array of 1/0, which is used in SUMPRODUCT multi-conditional tests.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nigel" wrote in message
...
Hi Bob
Just what the doctor ordered, works a treat, many thanks. As an aside,
what
do the 2 hyphens do, as it seems to work with or without them?

"Bob Phillips" wrote:

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

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Nigel" wrote in message
...
I have a list of companies which occur more than once in a column. I
want
to
be able to count how many different companies there are, not how many
times
they occur. Any help, please.

Thanks