# counting duplicates within array formula?

1. ## counting duplicates within array formula?

range1-> 1 2 3 1 2 3
range2-> a c a a b a

How do I count duplicates within an array formula?

Say I generated an array formula range2= "a"*range1 I would get an array
{1,0,3,1,0,3}

How do I write the formula to search within that array to count the number of entries that appear exactly twice and how high it is. (3 for the example above. Second highest is 1).  Register To Reply

2. Originally Posted by etmac
range1-> 1 2 3 1 2 3
range2-> a c a a b a

How do I count duplicates within an array formula?

Say I generated an array formula range2= "a"*range1 I would get an array
{1,0,3,1,0,3}

How do I write the formula to search within that array to count the number of entries that appear exactly twice and how high it is. (3 for the example above. Second highest is 1).
1. For counting those that appear exactly twice, try the following:

=SUM(IF(COUNTIF(\$A\$1:\$F\$1,A1:F1)=2,1,0))/2

Entered with CTRL+SHIFT+ENTER.

2. For counting the highest:

(A) If you mean the highest of them all:

=MAX(\$A\$1:\$F\$1)

(B) If you mean the highest of the ones that appear twice:

=MAX(IF(COUNTIF(\$A\$1:\$F\$1,A1:F1)=2,A1:F1,""))

Entered with CTRL+SHIFT+ENTER.

Scott  Register To Reply

3. Oh thank you for the reply. I've been stuck for days trying to figure this out.

Yes the formulas that you wrote do what I was looking for except is it possible to write it so that it works on the array in "memory"

so to continue my example...
(range1 is a1:f1, range2 is a2:f2)
(actual data on the worksheet)
range1-> 1 2 3 1 2 3
range2-> a c a a a a

...the array within which I'm looking duplicates for is
{(a1:f2)*(a1:f2="a")}
which would be {1,0,3,1,2,3}

Do I have to have that array on the worksheet somewhere or can i work on it within "memory"? I tried just pasting that formula into the ones you provided but it didn't seem to work.
i.e. (=SUM(IF(COUNTIF((\$a\$1:\$f\$2)*(\$a\$1:\$f\$2="a"),(a1:f2)*(a1:f2="a"))=2,1,0))/2)  Register To Reply

4. To do it all in memory will take someone other than me to figure out. I suppose you could write a custom function to do it in VBA.

Scott  Register To Reply

5. Try

=SUM(--(FREQUENCY(IF(A2:F2="a",A1:F1),IF(A2:F2="a",A1:F1))=2))

confirmed with CTRL+SHIFT+ENTER  Register To Reply

6. thanks daddy for the reply. it seems like your formula provides the number of pairs found in the array. While this is useful I'm looking for the highest value of the "pairs". In the above example the highest pair would be 3. The second highest is 1.  Register To Reply