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]

First off, you can remove the double negatives since your multiplication takes care of the converting Booleans to numeric equivalents automatically. Now, a long time ago when I used to post using multiplications inside the SUMPRODUCT function instead of listing the terms, I was advised by several then long-time highly knowledgeable volunteers that doing so was not a good idea. It was so long ago now that I do not remember the exact details but I believe it had something to do with how errors are handled by the two methods.

 

[mehidy1437]

@Rick Rothstein , thanks for the information, will keep in mind.

 

[cortexnotion]

Thank you @Fluff and @Rick Rothstein. I was indeed looking to count occurrences of any number appearing two or more times

Both formulas do the trick, thank you for the help!

 

[Fluff]

Glad we could help & thanks for the feedback.