Counting duplicate values

[frankiejr101]

Not the usual. If A1:A7 is:
1
1
2
3
4
4
4

Then I'd like to have a formula that tells me how many duplicates it finds. So in this case B1 (where the formula would reside) would return a value of 2 since there were 2 sets of duplicates. Is this possible? Thanks!

 

[Andrew Poulsom]

Try:

=SUMPRODUCT((1/COUNTIF(A1:A7,A1:A7)),--(COUNTIF(A1:A7,A1:A7)>1))

 

[kevatarvind]

try below one

=COUNTIF(A:A,A1)

 

[Rick Rothstein]

This formula appears to work also...

=SUMPRODUCT((COUNTIF(A1:A7,A1:A7)>1)*(A1:A7<>A2:A8))

Note the range offset by one row for that last comparison... you will need to keep it in mind when you expand the formula to handle more rows than the 7 it was designed for.

 

[frankiejr101]

ADVERTISEMENT

This formula appears to work also...

=SUMPRODUCT((COUNTIF(A1:A7,A1:A7)>1)*(A1:A7<>A2:A8))

Note the range offset by one row for that last comparison... you will need to keep it in mind when you expand the formula to handle more rows than the 7 it was designed for.

This did not work though the first countif did. I wonder if it has anything to do with the fact that my values are actually text and I just used numbers for ease in my example???

 

[Andrew Poulsom]

Is your data sorted? The formula I posted works with text and numbers.

 

[Aladin Akyurek]

Not the usual. If A1:A7 is:
1
1
2
3
4
4
4

Then I'd like to have a formula that tells me how many duplicates it finds. So in this case B1 (where the formula would reside) would return a value of 2 since there were 2 sets of duplicates. Is this possible? Thanks!

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A8<>"",MATCH(A2:A8,A2:A8,0)),ROW(A2:A8)-ROW(A2)+1)>1,1))