Count cells containing un-unique values in range

[BWMagee]

I have a range of values G3:DB15. In that range, some values appear just once, while others appear multiple times. I want to count the number of cells containing a value which appears more than once. (Or, count the total number of cells in range, and subtract the number of cells with values appearing just once).
So if 3 was the only value to appear more than once, and it appeared twice, the count would be 2. If 3 only appeared once, the count would be 0. If 3 appeared twice, and 4 appeared four times, the count would be 6.
Help is much appreciated!

 

[jasonb75]

This should do it

=SUMPRODUCT(--(FREQUENCY(G3:DB15,G3:DB15)>1))

 

[BWMagee]

This should do it

=SUMPRODUCT(--(FREQUENCY(G3:DB15,G3:DB15)>1))

Thanks for your reply. Unfortunately its not returning the correct result. I've narrowed the range to G3:I14 so I can count all the occurrences myself. Inputing this range into your formula returns an answer of 7. However I count a 4 unique values that appear more than once, each appearing twice. So the answer should be 8.

The data below is columns G:I, and rows 3:14.
We can see the values 3, 3.2, 3.6 and 3.75 all appear twice. Every other value only appears once.

1​ 2​ 3​ 1.066666667​ 2.133333333​ 3.2​ 1.125​ 2.25​ 3.375​ 1.2​ 2.4​ 3.6​ 1.25​ 2.5​ 3.75​ 1.333333333​ 2.666666667​ 4​ 1.40625​ 2.8125​ 4.21875​ 1.5​ 3​ 4.5​ 1.6​ 3.2​ 4.8​ 1.666666667​ 3.333333333​ 5​ 1.8​ 3.6​ 5.4​ 1.875​ 3.75​ 5.625​

 

[jasonb75]

Try one of these instead, both give a count of 8 with the sample table.

=SUMPRODUCT(--TEXT(FREQUENCY(G3:DB15,G3:DB15),"[>1]#0;\0;\0"))

=COUNT($G$3:$I$14)-SUMPRODUCT(--(COUNTIF(G3:I14,G3:I14)=1))

I had made a mistake in the way that I set the formula up, which meant it would only count each duplicate once. With the table above though, it should have given you 4, not 7.
If you have any additional discrepancies then they could be down to decimal precision / rounding.

 

[DanteAmor]

Another formula you could consider.
Note: Array Formula to accept you must press

Book1
	G	H	I	J
3	1	2	3	8
4	1.066666667	2.133333333	3.2
5	1.125	2.25	10
6	1.2	2.4	3.6
7	1.25	2.5	3.75
8	1.333333333	2.666666667	4
9	1.40625	2.8125	4.21875
10	1.5	3	4.5
11	1.6	3.2	4.8
12	1.666666667	3.333333333	5
13	1.8	3.6	5.4
14	1.875	3.75	5.62
Sheet

Cell Formulas
Range	Formula
J3	J3	{=(COUNT(G3:I14)-SUM(IF(FREQUENCY(G3:I14,G3:I14),1)))*2}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[BWMagee]

Try one of these instead, both give a count of 8 with the sample table.

=SUMPRODUCT(--TEXT(FREQUENCY(G3:DB15,G3:DB15),"[>1]#0;\0;\0"))

=COUNT($G$3:$I$14)-SUMPRODUCT(--(COUNTIF(G3:I14,G3:I14)=1))

I had made a mistake in the way that I set the formula up, which meant it would only count each duplicate once. With the table above though, it should have given you 4, not 7.
If you have any additional discrepancies then they could be down to decimal precision / rounding.

The second one works perfectly! I get 8 when used on the sample table above, and the correct values when expanding it to the rest of the table.
The first one returns a value of 6 for me when used on the range G3:I14. But not to worry, I'll use the second.

 

[BWMagee]

Another formula you could consider.
Note: Array Formula to accept you must press

Book1
	G	H	I	J
3	1	2	3	8
4	1.066666667	2.133333333	3.2
5	1.125	2.25	10
6	1.2	2.4	3.6
7	1.25	2.5	3.75
8	1.333333333	2.666666667	4
9	1.40625	2.8125	4.21875
10	1.5	3	4.5
11	1.6	3.2	4.8
12	1.666666667	3.333333333	5
13	1.8	3.6	5.4
14	1.875	3.75	5.62
Sheet

Cell Formulas
Range	Formula
J3	J3	{=(COUNT(G3:I14)-SUM(IF(FREQUENCY(G3:I14,G3:I14),1)))*2}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Thanks for your reply. Similar to one of jasonb75's formulas above, this one returns a value of 6 when I use it on the range G3:I14. I can see it returns the value 8 in your example, so I'm not sure why it would be different with mine. The numbers in the table are the same. Perhaps the only difference is that my numbers are themselves calculated by a formula?

 

[DanteAmor]

Check the number of decimals in the result of each formula, since it is not the same 3.333333 as 3.333334. Maybe if you round the result of your formulas to 2 or 3 decimals.

 

[jasonb75]

I was wondering if it was rounding as well, Dante (as mentioned in my previous reply) but if that was the case then surely both of my suggestions would have given the same incorrect result as well?

With your formula, you appear to have only allowed for duplicates, but not for the scenario given in post #1.

If 3 appeared twice, and 4 appeared four times, the count would be 6.

Another idea that came to mind, bug given how unrelaible frequency appears to be for this task

=SUMPRODUCT(--NOT(FREQUENCY(G3:I14,G3:I14)=1))-1

 

[BWMagee]

It is a curious one. None of the numbers are rounded, and I'd prefer to leave it that way for accuracy. But even for jasonb75's correct formula, the odd value is still being ignored. I can clearly see 4.21399177 appearing in two different places in the table, yet not counted in the result. No rounding has been applied (unless excel is doing its own rounding behind the scenes?)

I have conditional formatting on the same section, which gets the same result as jasonb75's correct formula, including ignoring that 4.21399177 value.

conditional formatting =IF((COUNTIF($D$7:$CY$18,D7)-COUNT(D7)),1,0)

(Range position has adjusted slightly from original post, from 3:14 to 7:18)

I don't really need 100% accuracy on this and can accept the odd number being missed, but it is curious.

The latest NOT(FREQUENCY formula also does not produce correct results.