Sum in numbers for text values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

asgh77 - 25 Jul 2006 14:54 GMT

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

Signature

asgh77

Reply to this Message

Bernie Deitrick - 25 Jul 2006 15:25 GMT

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

> 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
[quoted text clipped - 7 lines]
>
> Andy

Reply to this Message

SteveW - 25 Jul 2006 15:46 GMT

Neat - never thought of using that trick :)

Steve

> Andy,
>
[quoted text clipped - 19 lines]
>>
>> Andy

Reply to this Message

Kernow Girl - 25 Jul 2006 15:38 GMT

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

> 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
[quoted text clipped - 7 lines]
>
> Andy

Reply to this Message

asgh77 - 25 Jul 2006 15:49 GMT

Thanks Bernie - that worked perfectly!

Regards

And

Reply to this Message

Rate this thread: