software

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:A4lt;gt;quot;quot;)/COUNTIF(A1:A4,A1:A4amp;quot;quot;))Regards,

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=526074Try something like this:

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

B1: =SUMPRODUCT((A2:A10lt;gt;quot;quot;)/COUNTIF(A2:A10,A2:A10amp;quot;quot;))

Does that help?

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

XL2002, WinXP-Proquot;vishuquot; wrote:

gt; Hi,
gt; I have list of names. Some of the names are repeating twice or thrice.
gt; I want to count the names. If i put the count formula it will count repeated
gt; names also.
gt; Is there any formula to count wherein it should consider repeated name as 1
gt;
gt; Name
gt; MR.A
gt; MR.B
gt; MR.C
gt; MR.A
gt;
gt; The result should be 3 to above example

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 lt;gt;quot;quot; and amp;quot;quot;
Please help me.
regards
vishu

quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; for a list of values in A1:A10 with A1 being the column heading:
gt;
gt; B1: =SUMPRODUCT((A2:A10lt;gt;quot;quot;)/COUNTIF(A2:A10,A2:A10amp;quot;quot;))
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;vishuquot; wrote:
gt;
gt; gt; Hi,
gt; gt; I have list of names. Some of the names are repeating twice or thrice.
gt; gt; I want to count the names. If i put the count formula it will count repeated
gt; gt; names also.
gt; gt; Is there any formula to count wherein it should consider repeated name as 1
gt; gt;
gt; gt; Name
gt; gt; MR.A
gt; gt; MR.B
gt; gt; MR.C
gt; gt; MR.A
gt; gt;
gt; gt; The result should be 3 to above example

OK...Here you go:

Regarding:
=SUMPRODUCT((A2:A10lt;gt;quot;quot;)/COUNTIF(A2:A10,A2:A10amp;quot;quot;))

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

Within that formula's context
(A2:A10lt;gt;quot;quot;) 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:A10amp;quot;quot;) section, this part: A2:A10amp;quot;quot; 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-Proquot;vishuquot; wrote:

gt; Hi
gt; Thanks a lot..Its working fine.
gt; Can you tell me logics behind this formula. I know countif formula.But iam
gt; not able to understand logic in this forumula.
gt; Can you explain me logic behind using SUMPRODUCT and using lt;gt;quot;quot; and amp;quot;quot;
gt; Please help me.
gt; regards
gt; vishu
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; for a list of values in A1:A10 with A1 being the column heading:
gt; gt;
gt; gt; B1: =SUMPRODUCT((A2:A10lt;gt;quot;quot;)/COUNTIF(A2:A10,A2:A10amp;quot;quot;))
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;vishuquot; wrote:
gt; gt;
gt; gt; gt; Hi,
gt; gt; gt; I have list of names. Some of the names are repeating twice or thrice.
gt; gt; gt; I want to count the names. If i put the count formula it will count repeated
gt; gt; gt; names also.
gt; gt; gt; Is there any formula to count wherein it should consider repeated name as 1
gt; gt; gt;
gt; gt; gt; Name
gt; gt; gt; MR.A
gt; gt; gt; MR.B
gt; gt; gt; MR.C
gt; gt; gt; MR.A
gt; gt; gt;
gt; gt; gt; The result should be 3 to above example