Sum in numbers for text values

[asgh77]

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

[Bernie Deitrick]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Kernow Girl]

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

[asgh77]

Thanks Bernie - that worked perfectly!

Regards

Andy

[SteveW]

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)
>
> =3DSUM(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" <[email protected]> wrote i=
n =

> message
> news:[email protected]...
>>
>> 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 orde=
r
>> to total each column and how do I go about excluding duplicates?
>>
>> Thanks
>>
>> Andy
>>