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
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?
Hi putritersenyum,
This looks like a Pivot Table problem to me. Pivot tables can do either Count or Sum or BOTH. Read about them at:
http://www.pivot-table.com/2009/11/1...-sum-or-count/
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
This code work for me, but not for ignoring blank cells
=ROWS($S$10:$S$1000)-SUM(IF( COUNTIF($S$10:$S$1000;$S$10:$S$1000)=1;1;0))
Try this:
=SUMPRODUCT(--(COUNTIF(A1:A10,A1:A10)>1))
Great, it works. Thank you very much, 63falcondude
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<>""))
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks