Count duplicate values in a range

[cortexnotion]

Hi All,

I have a range A2:A75. I need a formula for the following:

For cells than are greater than 0, I need to count any value that appears more than once in the range. I need to count the 1st appearance of the value in addition to the duplicates.

Many thanks for your help

 

[Rick Rothstein]

Does this formula do what you want (place it in cell B2 and copy down)...

=IF(OR(A2<0,COUNTIF(A$2:A$75,A2)=1),"",COUNTIF(A$2:A$75,A2))

 

[cortexnotion]

Hi Rick, thank you for your quick reply.

I was hoping to avoid using a helper column, if there is a single formula if possible? Thanks!

 

[Rick Rothstein]

I am unclear how you want that presented. How will the formula know what number to count? Can you describe what you want the complete output to be?

 

[scottleger4]

If you only need a count, highlight the range and then click on the "Remove Duplicates" button in the ribbon. Once the process finishes, it will have a little pop up window that will tell you how many unique values and how many unique vales are there. After you have your count, just click undo to put everything back to the way it was.

 

[Fluff]

Can't remember if this works with 2013, or only 365

=COUNT(IF(COUNTIFS(A2:A75,A2:A75,A2:A75,">0")>1,A2:A75))

Needs to be confirmed with Ctrl Shift Enter, rather than just Enter.

 

[Rick Rothstein]

@Fluff,

If it is meant to all instances of numbers that appear two or more times (is that what the OP was asking for?), then it works in XL2010.

 

[Fluff]

I think that's what the OP is after (but could easily be wrong).
And thanks for confirming it works in older versions of Excel.

 

[Rick Rothstein]

This normally-entered formula also seems to work (it returns the same answer as your formula)...

=SUMPRODUCT(0+(COUNTIF(A2:A75,A2:A75)>1),0+(A2:A75>0))

 

[mehidy1437]

Hello Seniors, what're your comments on this?

=SUMPRODUCT(--(COUNTIF(A2:A75,A2:A75)>=2)*((A2:A75)>0))