Counting Individual Companies

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi Pranav

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

 

[Bob Phillips]

=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)

 

[Guest]

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]

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)

 

[Bobito]

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