How to count all of duplicate value by ignoring blank cells?

Please, help me to count sum of duplicate values by ignoring blank cells.

Name
===== Duplicate value = 3
1 Apple
2 Mango
3 Apple
4 Apple
5 Papaya
6 Banana
7
8
9
10

can you post a sample file? Need more details. What if two values are duplicated?

This code work for me, but not for ignoring blank cells

Please Login or Register  to view this content.

Try this:

=SUMPRODUCT(--(COUNTIF(A1:A10,A1:A10)>1))

63falcondude, my range have formula, so although the value is blank but the formula for one cell count as one.

In that case, try this:

=SUMPRODUCT(--(COUNTIFS(A1:A10,A1:A10)>1)*(A1:A10<>""))

Great, it works. Thank you very much, 63falcondude

You're welcome. Thanks for the rep!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.