# Counting duplicate values

#### frankiejr101

##### Board Regular
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!

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Andrew Poulsom

##### MrExcel MVP
Try:

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

try below one

=COUNTIF(A:A,A1)

#### Rick Rothstein

##### MrExcel MVP
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

##### Board Regular
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

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

##### MrExcel MVP
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))

Replies
4
Views
251
Replies
4
Views
105
Replies
0
Views
122
Replies
3
Views
108
Replies
3
Views
229

1,195,625
Messages
6,010,754
Members
441,568
Latest member
abbyabby

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back