COUNT forumula problem

V

vishu

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
 
S

SteveG

vishu,

Assuming your range is in A1:A4,

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


Regards,

Steve
 
R

Ron Coderre

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
 
V

vishu

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
 
R

Ron Coderre

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
 
Top