Help! I have been asked by work to create a spreadsheet for tracking the
number of companies we are speaking to. I need to calculate the total
number of companies in a certain column but some companies are listed
twice as we have different contacts in each company.

Do I need to somehow convert the text in to a numerical value in order
to total each column and how do I go about excluding duplicates?

Thanks

Andy


--
asgh77
------------------------------------------------------------------------
asgh77's Profile: http://www.excelforum.com/member.php...o&userid=36700
View this thread: http://www.excelforum.com/showthread...hreadid=564707

Andy,

The array formula (entered using Ctrl-Shift-Enter)

=SUM(IF(A1:A1000<"",1/COUNTIF(A1:A1000,A1:A1000)))

will count the number of unique entries in A1:A1000.

HTH,
Bernie
MS Excel MVP


"asgh77" wrote in message
...

Help! I have been asked by work to create a spreadsheet for tracking the
number of companies we are speaking to. I need to calculate the total
number of companies in a certain column but some companies are listed
twice as we have different contacts in each company.

Do I need to somehow convert the text in to a numerical value in order
to total each column and how do I go about excluding duplicates?

Thanks

Andy


--
asgh77
------------------------------------------------------------------------
asgh77's Profile: http://www.excelforum.com/member.php...o&userid=36700
View this thread: http://www.excelforum.com/showthread...hreadid=564707

Hi asgh77 -

try Data / Subtotals -

I ended up with the column below showing each value and how many, I then did
a bog standard count at the bottom (does not count text values).

any
any
any
anyCount 3
fred
fred Count 1
gowe
gowe Count 1
jowe
jowe Count 1
hopt
hopt Count 1

5
Hope this helps --- Dika



"asgh77" wrote:


Help! I have been asked by work to create a spreadsheet for tracking the
number of companies we are speaking to. I need to calculate the total
number of companies in a certain column but some companies are listed
twice as we have different contacts in each company.

Do I need to somehow convert the text in to a numerical value in order
to total each column and how do I go about excluding duplicates?

Thanks

Andy


--
asgh77
------------------------------------------------------------------------
asgh77's Profile: http://www.excelforum.com/member.php...o&userid=36700
View this thread: http://www.excelforum.com/showthread...hreadid=564707

Neat - never thought of using that trick :)


Steve



On Tue, 25 Jul 2006 15:25:34 +0100, Bernie Deitrick <deitbe consumer dot
org wrote:

Andy,

The array formula (entered using Ctrl-Shift-Enter)

=SUM(IF(A1:A1000<"",1/COUNTIF(A1:A1000,A1:A1000)))

will count the number of unique entries in A1:A1000.

HTH,
Bernie
MS Excel MVP


"asgh77" wrote in
message
...

Help! I have been asked by work to create a spreadsheet for tracking the
number of companies we are speaking to. I need to calculate the total
number of companies in a certain column but some companies are listed
twice as we have different contacts in each company.

Do I need to somehow convert the text in to a numerical value in order
to total each column and how do I go about excluding duplicates?

Thanks

Andy

Thanks Bernie - that worked perfectly!

Regards

Andy


--
asgh77
------------------------------------------------------------------------
asgh77's Profile: http://www.excelforum.com/member.php...o&userid=36700
View this thread: http://www.excelforum.com/showthread...hreadid=564707