countif returns 2 for number in range

[ajm]

then when doing a find over that range, there is only one of that number. how does that happen and how do i avoid it happening?

 

[MARK858]

Please post an example of where this situation happens using XL2BB

 

[ajm]

i can't provide the full data set but here is a sample of what's happening. you will see 03810 appears twice and 03237 says its there twice but is only there once.

FY24 Spend - Banded Top100,200.100, Supply Nation.xlsx
	H	I	J
119	$470,324	1	00304
120	$461,195	1	00865
121	$449,147	1	00478
122	$446,711	1	03719
123	$440,529	2	03237
124	$433,135	1	00756
125	$409,933	1	03704
126	$408,109	1	00239
127	$405,188	1	00345
128	$403,269	1	02704
129	$265,243	2	03810
130	$131,392	2	03810
131	$385,516	1	00710
132	$384,969	1	03029
133	$380,805	1	03457
Supplier Banding (3)

Cell Formulas
Range	Formula
I119:I133	I119	=COUNTIF($J$6:$J$844,J119)
J119:J133	J119	=XLOOKUP(F119,'C:\Documents\Spend Categorisation\[FY24 Categorised Spend.xlsb]RAW'!$K$2:$K$28868,'C:\Documents\Spend Categorisation\[FY24 Categorised Spend.xlsb]RAW'!$J$2:$J$28868)

 

[Peter_SSs]

here is a sample of what's happening.

What is happening? What is wrong?

 

[MARK858]

03237 says its there twice but is only there once.

As per what Peter states, how can we tell that the data is only there once when your formula goes from $J$6 to $J$844 and you only show us 119:133, we need representative data where we can see the issue occurring?
Are there hidden or filtered rows on your actual data and you only want to countif the visible rows?

 

[Peter_SSs]

03237 says its there twice but is only there once.

Put this formula in a vacant cell. It should tell you where the two values are.

=TEXTJOIN(", ",,"J"&FILTER(ROW(J6:J844),J6:J844=J123))

 

[ajm]

I manually overwrote the issue at the time but will go back to it and try the textjoin formula Peter. Thanks.

Mark, there were no filtered rows above the section I pasted in. I'll have a crack at Peter's formula above to identify where the second value is (if at all). if that gives me no joy, I'll post the whole thing, rather than just those rows above.. Cheers

 

[Alex Blakenburg]

If Peter's TextJoinFilter only returns 1 value then try filtering column J by typing into the filter search box.
CountIf would count both the text value 03237 and the numeric value 3237 while the filter function will only bring back the exact match.