Counting duplicate values in column

[Romano_odK]

Good morning,

I would like to count the double values in this column with a formula. How can I do that?
Thank you for your time.

Romano

XML artikel import met Item refresh 1.05.xlsm
	B
816	8712759036033
817	8712759036057
818	8712759036040
819	8712759036064
820	8712759036071
821	8712759076602
822	8712759342004
823	8712759341007
824	8712759077890
825	4041271002016
826	4260385343680
827	4041271002023
828	4260385343703
829	4041271002047
830	4260385343727
831	4041271002054
832	4260385343741
833	4041271040858
834	4260385343765
835	4041271040865
836	4260385343789
837	4041271002061
838	4260385343802
839	4041271002085
840	4260385343826
841	4041271002092
842	4260385343840
843	4041271044085
844	4260385343864
845	4041271044092
846	4260385343888
847	4041271044108
848	4260385343901
849	4041271044115
850	4260385343925
851	4041271044122
852	4260385343949
853	4041271044139
854	4041271002108
855	4260385343987
856	4041271002115
857	4260385344007
858	4041271002122
859	4260385344021
860	4041271002139
861	4260385344045
Items

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B6:B10065	Cell Value	duplicates	text	NO

 

[etaf]

i cant see any duplicates , using a countif()
which ones a double

Book18
	B	C
816	8712759036033.00	1
817	8712759036057.00	1
818	8712759036040.00	1
819	8712759036064.00	1
820	8712759036071.00	1
821	8712759076602.00	1
822	8712759342004.00	1
823	8712759341007.00	1
824	8712759077890.00	1
825	4041271002016.00	1
826	4260385343680.00	1
827	4041271002023.00	1
828	4260385343703.00	1
829	4041271002047.00	1
830	4260385343727.00	1
831	4041271002054.00	1
832	4260385343741.00	1
833	4041271040858.00	1
834	4260385343765.00	1
835	4041271040865.00	1
836	4260385343789.00	1
837	4041271002061.00	1
838	4260385343802.00	1
839	4041271002085.00	1
840	4260385343826.00	1
841	4041271002092.00	1
842	4260385343840.00	1
843	4041271044085.00	1
844	4260385343864.00	1
845	4041271044092.00	1
846	4260385343888.00	1
847	4041271044108.00	1
848	4260385343901.00	1
849	4041271044115.00	1
850	4260385343925.00	1
851	4041271044122.00	1
852	4260385343949.00	1
853	4041271044139.00	1
854	4041271002108.00	1
855	4260385343987.00	1
856	4041271002115.00	1
857	4260385344007.00	1
858	4041271002122.00	1
859	4260385344021.00	1
860	4041271002139.00	1
861	4260385344045.00	1
Sheet3

Cell Formulas
Range	Formula
C816:C861	C816	=COUNTIF($B$816:$B$861,B816)

 

[Peter_SSs]

i cant see any duplicates ,

It appears the OP's data is much bigger than shown:

I would like to count the double values in this column

Can a number occur more than twice?
If so, what would be the count for this sample data?

23 05 22.xlsm
	E
803	5
804	6
805	3
806	5
807	5
808	3
809	4
Sheet2 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E803:E809	Cell Value	duplicates	text	NO

In any case, even if a number only occurs twice, how do you want the count to be? For example, is the count for this sample data 1 or 2?

23 05 22.xlsm
	G
803	5
804	6
805	5
806	3
Sheet2 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G803:G806	Cell Value	duplicates	text	NO

 

[Romano_odK]

It appears the OP's data is much bigger than shown:
View attachment 92032


Can a number occur more than twice?
If so, what would be the count for this sample data?

23 05 22.xlsm
	E
803	5
804	6
805	3
806	5
807	5
808	3
809	4
Sheet2 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E803:E809	Cell Value	duplicates	text	NO

In any case, even if a number only occurs twice, how do you want the count to be? For example, is the count for this sample data 1 or 2?

23 05 22.xlsm
	G
803	5
804	6
805	5
806	3
Sheet2 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G803:G806	Cell Value	duplicates	text	NO

If would have 1 1 1 and 2 2 I would like to have the number 5 times. Is that possible?

 

[Peter_SSs]

If would have 1 1 1 and 2 2 I would like to have the number 5 times.

You would have to adapt to your range, but would this suffice?

23 05 22.xlsm
	E	F
803	5	5
804	6
805	3
806	5
807	5
808	3
809	4
Count Dupes

Cell Formulas
Range	Formula
F803	F803	=SUM(--(COUNTIFS(E803:E809,E803:E809)>1))

 

[Romano_odK]

You would have to adapt to your range, but would this suffice?

23 05 22.xlsm
	E	F
803	5	5
804	6
805	3
806	5
807	5
808	3
809	4
Count Dupes

Cell Formulas
Range	Formula
F803	F803	=SUM(--(COUNTIFS(E803:E809,E803:E809)>1))

I tried it but the result is 0 which is should not be. Also and that I did not mention, its in a table.

 

[Peter_SSs]

I tried it but the result is 0 which is should not be. Also and that I did not mention, its in a table.

Table should not be the problem. This is a table & the formula has counted the blue & green cells.
Test the concept by making up a small dummy table like this and try the formula on that table.

Romano_odK.xlsm
	B	C	D
801
802	Number
803	8712759036033		5
804	8712759036057
805	8712759036040
806	9999759036057
807	8712759036040
808	8712759036040
809	8712759036057
810	6598985454872
811
Count Dupes

Cell Formulas
Range	Formula
D803	D803	=SUM(--(COUNTIFS(Table1[Number],Table1[Number])>1))

 

[Romano_odK]

Table should not be the problem. This is a table & the formula has counted the blue & green cells.
Test the concept by making up a small dummy table like this and try the formula on that table.

Romano_odK.xlsm
	B	C	D
801
802	Number
803	8712759036033		5
804	8712759036057
805	8712759036040
806	9999759036057
807	8712759036040
808	8712759036040
809	8712759036057
810	6598985454872
811
Count Dupes

Cell Formulas
Range	Formula
D803	D803	=SUM(--(COUNTIFS(Table1[Number],Table1[Number])>1))

I figured it out. Thank you.

 

[Peter_SSs]

I figured it out. Thank you.

Glad you sorted it out. Thanks for the confirmation.