Count Unique text in 3 different columns

[Vitordf]

Hi,

I saw a few formulas that adds Unique records when is numbers, but I have a
spreasheet which I need to count the Unique records (Names) taken into
consideration three diffrent columns, example;
Column A Column B Column C
DC&D aagis Ternure
DC&D aagis Ternure
DC&D adamk New Hire
DC&D adamk New Hire
DC&D adasilva New Hire
DC&D adasilva New Hire
DC&D vdafons Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
DC&D AMPATA Ternure
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO Ashaw New Hire
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure
AIO buyssp Ternure

Hope someone could help me.

Many Thanks,

 

[Eduardo]

Hi,
what are the results you are looking for ?

 

[Mike H]

Hi,

I'm not sure if you want each column or a single count for the 3 columns.
Try these ARRAY formula

All Columns
=SUM(IF(LEN(A1:C23),1/COUNTIF(A1:C23,A1:C23)))

A single column. Drag right for cols B & C
=SUM(IF(LEN(A1:A23),1/COUNTIF(A1:A23,A1:A23)))

These are array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Vitordf]

Hi, Sorry, I am looking to have the Unique Number of Names (Column B) where
they are Ternured or New Hire (Column C) for each of the Categories (Column
A) as per example would be something like;
Ternured - DC&D - 3 Names
New Hire - DC&D - 2 Names
Ternured - AIO - 1 Name
New Hire - AIO - 1 Name

There is also a possibility that 1 Name appear in both Categories (Column A),

Hope I have been a bit more clear,
Many Thanks for the help

 

[Vitordf]

Hi Mike,

Many Thanks for your reply and help, and the examples are great but I need
to go futher, I need to ascertain the number of Agents (Column B) for each of
the Categories in column A and C and were the same agent can be in both
category in Column A... in fact I need to get the Total Unique Names of New
Hire/Ternured for each of the Category DC&D/AIO.

Regs,

 

[Teethless mama]

=SUM(N(FREQUENCY(MATCH(A1:A23&B1:B23&C1:C23,A1:A23&B1:B23&C1:C23,0),MATCH(A1:A23&B1:B23&C1:C23,A1:A23&B1:B23&C1:C23,0))>0))

 

[Teethless mama]

Assuming your data in a1:c23

criteria
E1: DC&D F1: Ternure
E2: DC&D F2: New Hire
and so on...

G1:
=SUM(N(FREQUENCY(IF(($A$1:$A$23=E1)*($C$1:$C$23=F1),MATCH($B$1:$B$23,$B$1:$B$23,)),MATCH($B$1:$B$23,$B$1:$B$23,))>0))

ctrl+shift+enter, not just enter
copy down as far as needed

 

[Vitordf]

Hi,

Again many Thanks, and yes the formula you gave me is great and very useful
for other applications I have, but for this particular exercise I need to
have the result as:

DC&D AIO
New Hire 2 1
Ternure 3 1

I need to ascertain the Number of New Hires and Ternured in each category.

Thanks,

 

[Vitordf]

many Thanks for your GREAT Help, this is exactly what I was looking for, you
have saved a lot of working hours... )

 

[Herbert Seidenberg]

Excel 2007 PivotTable
One formula used:
=1/COUNTIF([Name],[Name])
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_06_10.xlsx
Pdf preview:
http://www.mediafire.com/file/yim4emdgnum/04_06_10.pdf