COUNT forumula problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have list of names. Some of the names are repeating twice or thrice.
I want to count the names. If i put the count formula it will count repeated
names also.
Is there any formula to count wherein it should consider repeated name as 1

Name
MR.A
MR.B
MR.C
MR.A

The result should be 3 to above example
 
vishu,

Assuming your range is in A1:A4,

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


Regards,

Steve
 
Try something like this:

for a list of values in A1:A10 with A1 being the column heading:

B1: =SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Hi
Thanks a lot..Its working fine.
Can you tell me logics behind this formula. I know countif formula.But iam
not able to understand logic in this forumula.
Can you explain me logic behind using SUMPRODUCT and using <>"" and &""
Please help me.
regards
vishu
 
OK...Here you go:

Regarding:
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))

First...to give credit where credit is due:
That formula was developed by Aladin Akyurek and Harlan Grove.

Within that formula's context
(A2:A10<>"") checks if a cell value does not equal an empty string. It
returns a 1 for non-blanks and a 0 for blanks.

In the COUNTIF(A2:A10,A2:A10&"") section, this part: A2:A10&"" ensures that
the COUNTIF function will always return at least 1 and never 0 (which would
cause the formula to error out when the division is performed.)

Consequently, the numerator for blank cells is 0, so they are not counted.
The numerator for all non-blanks is 1.
The denominator for all non-blanks is their count.

If a value appears 3 times, three of the numerators will be 1 and their
respective denominators will be 3. The SUMPRODUCT function will add those
three fractions: (1/3)+(1/3)+(1/3)=1
That's how the three occurrences only count as a single instance of a unique
value.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Back
Top