Count Question

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have a list that contains about 45 different employee names. Many of
the names are listed more than once and the number of names on the list
is constantly changing. The number of rows is about 200. I'd like to
know if there is a formula that will allow me to count the total number
of names with each name being counted only once?

Thanks so much for your help.

Mike
 
Hi Mike

here's an array formula (enter with ctrl, shift & enter) from chip pearson's
website (www.cpearson.com) that does this for you

=SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""),IF(LEN(Range1)
0,MATCH(Range1,Range1,0), ""))>0,1))
Click to expand...

(all on one line)
assumptions, the names are in a range name called Range1
you can make this range dynamic if you want (see notes at
www.contextures.com/tiptech.html on dynamic range names)

Cheers
JulieD
 
Slightly shorter and *not* an array formula:

=SUMPRODUCT((A1:A200<>"")/COUNTIF(A1:A200,A1:A200&""))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Hi Mike

here's an array formula (enter with ctrl, shift & enter) from chip pearson's
website (www.cpearson.com) that does this for you

=SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""),IF(LEN(Range1)
0,MATCH(Range1,Range1,0), ""))>0,1))
Click to expand...

(all on one line)
assumptions, the names are in a range name called Range1
you can make this range dynamic if you want (see notes at
www.contextures.com/tiptech.html on dynamic range names)

Cheers
JulieD
 
Back
Top