repetitive

Hi, anybody can help me to create formula for this case.
Sample Data
1. ABC
2. AAA
3. ABC
4. 555
5. AAA
6. ABC

How to count the data turn up more than one
answer for this case : 2 data
(ABC = 3times, AAA=2times)

Hi,

You will use a simple combination of countif() formulas to accomplish this.

Basically, next to each cell you would do a countif(range, cell); then at
the bottom fo the new list you would do a countd(original range) -
countif(new range, ">1"

Regards,
Hany

"Repetitive formula" wrote:

> Hi, anybody can help me to create formula for this case.
> Sample Data
> 1. ABC
> 2. AAA
> 3. ABC
> 4. 555
> 5. AAA
> 6. ABC
>
> How to count the data turn up more than one
> answer for this case : 2 data
> (ABC = 3times, AAA=2times)

Hi!

Here's another way:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=SUM(--(FREQUENCY(IF(A1:A6<>"",MATCH(A1:A6&"",A1:A6&"",0)),MATCH(A1:A6&"",A1:A6&"",0))>1))

Biff

"Repetitive formula" <Repetitive formula@discussions.microsoft.com> wrote in
message news:9CEF58ED-8F27-4F8E-ADB8-3F47F9D7C9F0@microsoft.com...
> Hi, anybody can help me to create formula for this case.
> Sample Data
> 1. ABC
> 2. AAA
> 3. ABC
> 4. 555
> 5. AAA
> 6. ABC
>
> How to count the data turn up more than one
> answer for this case : 2 data
> (ABC = 3times, AAA=2times)