repetitive

R

Repetitive formula

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)
 
H

Hany ElKady

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
 
B

Biff

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
 
Top