im trying to use a countif but its returning the wrong count

[Reecenorman1996]

i have a column in a table which is using a formula to calculate a percentage , i used this formula =COUNTIF(Table1[%],"<5%") but it returned the wrong number and i have no idea why

 

[Sufiyan97]

Try

=COUNTIF(Table1[%],"<0.05")

 

[Fluff]

Can you post some sample data that shows the problem.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Reecenorman1996]

Can you post some sample data that shows the problem.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

i downloaded but its greyed out

 

[Fluff]

Have a look here XL2BB Icons greyed out

 

[Reecenorman1996]

PR 07-08-2023.xlsx
	F	G	H	I	J	K	L	M
1	Old Price	Reduction	%	new price		Portal bandings
2	£9,000,000	£8,000,000	89%	£1,000,000		£260,000		Total PR
3	£110,000	£30,000	27%	£80,000		£270,000
4	£350,000	£75,000	21%	£275,000		£280,000
5	£150,000	£30,000	20%	£120,000		£290,000
6	£10,000	£2,000	20%	£8,000		£300,000
7	£10,000	£2,000	20%	£8,000		£325,000		Count if under 5%
8	£10,000	£2,000	20%	£8,000		£350,000		54
9	£110,000	£20,000	18%	£90,000		£375,000
10	£600,000	£100,000	17%	£500,000		£400,000
11	£415,000	£65,000	16%	£350,000		£425,000
12	£350,000	£50,000	14%	£300,000		£450,000
13	£120,000	£16,000	13%	£104,000		£475,000
14	£275,000	£35,000	13%	£240,000		£500,000
15	£325,000	£40,000	12%	£285,000		£550,000
16	£325,000	£40,000	12%	£285,000		£600,000
17	£1,250,000	£150,000	12%	£1,100,000		£650,000
18	£85,000	£10,000	12%	£75,000		£700,000
19	£190,000	£20,000	11%	£170,000		£800,000
20	£100,000	£10,000	10%	£90,000		£900,000
21	£100,000	£10,000	10%	£90,000		£1,000,000
22	£415,000	£40,000	10%	£375,000		£1,250,000
23	£210,000	£20,000	10%	£190,000		£1,500,000
24	£1,100,000	£100,000	9%	£1,000,000		£1,750,000
25	£1,100,000	£100,000	9%	£1,000,000		£2,000,000
26	£550,000	£50,000	9%	£500,000		£2,500,000
27	£550,000	£50,000	9%	£500,000		£3,000,000
28	£550,000	£50,000	9%	£500,000		£4,000,000
29	£220,000	£20,000	9%	£200,000		£5,000,000
30	£220,000	£20,000	9%	£200,000		£7,500,000
31	£220,000	£20,000	9%	£200,000
32	£875,000	£75,000	9%	£800,000
33	£435,000	£35,000	8%	£400,000
34	£250,000	£20,000	8%	£230,000
35	£650,000	£50,000	8%	£600,000
36	£650,000	£50,000	8%	£600,000
37	£325,000	£25,000	8%	£300,000
38	£325,000	£25,000	8%	£300,000
39	£325,000	£25,000	8%	£300,000
40	£325,000	£25,000	8%	£300,000
41	£325,000	£25,000	8%	£300,000
42	£325,000	£25,000	8%	£300,000
43	£325,000	£25,000	8%	£300,000
44	£325,000	£25,000	8%	£300,000
45	£325,000	£25,000	8%	£300,000
46	£292,000	£22,000	8%	£270,000
47	£700,000	£50,000	7%	£650,000
48	£700,000	£50,000	7%	£650,000
49	£350,000	£25,000	7%	£325,000
50	£350,000	£25,000	7%	£325,000
51	£350,000	£25,000	7%	£325,000
52	£350,000	£25,000	7%	£325,000
53	£140,000	£10,000	7%	£130,000
54	£725,000	£50,000	7%	£675,000
55	£290,000	£20,000	7%	£270,000
56	£1,495,000	£100,000	7%	£1,395,000
57	£750,000	£50,000	7%	£700,000
58	£750,000	£50,000	7%	£700,000
59	£750,000	£50,000	7%	£700,000
60	£750,000	£50,000	7%	£700,000
61	£375,000	£25,000	7%	£350,000
62	£375,000	£25,000	7%	£350,000
63	£375,000	£25,000	7%	£350,000
64	£300,000	£20,000	7%	£280,000
65	£150,000	£10,000	7%	£140,000
66	£800,000	£50,000	6%	£750,000
67	£800,000	£50,000	6%	£750,000
68	£800,000	£50,000	6%	£750,000
69	£400,000	£25,000	6%	£375,000
70	£400,000	£25,000	6%	£375,000
71	£400,000	£25,000	6%	£375,000
72	£400,000	£25,000	6%	£375,000
73	£400,000	£25,000	6%	£375,000
74	£400,000	£25,000	6%	£375,000
75	£400,000	£25,000	6%	£375,000
76	£240,000	£15,000	6%	£225,000
77	£160,000	£10,000	6%	£150,000
78	£399,950	£24,950	6%	£375,000
79	£165,000	£10,000	6%	£155,000
80	£850,000	£50,000	6%	£800,000
81	£425,000	£25,000	6%	£400,000
82	£425,000	£25,000	6%	£400,000
83	£425,000	£25,000	6%	£400,000
84	£425,000	£25,000	6%	£400,000
85	£425,000	£25,000	6%	£400,000
86	£170,000	£10,000	6%	£160,000
87	£170,000	£10,000	6%	£160,000
88	£690,000	£40,000	6%	£650,000
89	£350,000	£20,000	6%	£330,000
90	£530,000	£30,000	6%	£500,000
91	£450,000	£25,000	6%	£425,000
92	£550,000	£30,000	5%	£520,000
93	£550,000	£30,000	5%	£520,000
94	£275,000	£15,000	5%	£260,000
95	£370,000	£20,000	5%	£350,000
96	£950,000	£50,000	5%	£900,000
97	£475,000	£25,000	5%	£450,000
98	£475,000	£25,000	5%	£450,000
99	£475,000	£25,000	5%	£450,000
100	£475,000	£25,000	5%	£450,000
101	£190,000	£10,000	5%	£180,000
102	£1,250,000	£65,000	5%	£1,185,000
103	£600,000	£30,000	5%	£570,000
104	£500,000	£25,000	5%	£475,000
105	£500,000	£25,000	5%	£475,000
106	£300,000	£15,000	5%	£285,000
107	£220,000	£11,000	5%	£209,000
108	£200,000	£10,000	5%	£190,000
109	£200,000	£10,000	5%	£190,000
110	£200,000	£10,000	5%	£190,000
111	£200,000	£10,000	5%	£190,000
112	£525,000	£25,000	5%	£500,000
113	£525,000	£25,000	5%	£500,000
114	£315,000	£15,000	5%	£300,000
115	£210,000	£10,000	5%	£200,000
116	£210,000	£10,000	5%	£200,000
117	£210,000	£10,000	5%	£200,000
118	£210,000	£10,000	5%	£200,000
119	£550,000	£25,000	5%	£525,000
120	£550,000	£25,000	5%	£525,000
121	£220,000	£10,000	5%	£210,000
122	£220,000	£10,000	5%	£210,000
123	£445,000	£20,000	4%	£425,000
124	£450,000	£20,000	4%	£430,000
125	£230,000	£10,000	4%	£220,000
126	£575,000	£25,000	4%	£550,000
127	£230,000	£10,000	4%	£220,000
128	£600,000	£25,000	4%	£575,000
129	£600,000	£25,000	4%	£575,000
130	£625,000	£25,000	4%	£600,000
131	£625,000	£25,000	4%	£600,000
132	£625,000	£25,000	4%	£600,000
133	£650,000	£25,000	4%	£625,000
134	£650,000	£25,000	4%	£625,000
135	£280,000	£10,000	4%	£270,000
136	£280,000	£10,000	4%	£270,000
137	£280,000	£10,000	4%	£270,000
138	£280,000	£10,000	4%	£270,000
139	£280,000	£10,000	4%	£270,000
140	£280,000	£10,000	4%	£270,000
141	£285,000	£10,000	4%	£275,000
142	£290,000	£10,000	3%	£280,000
143	£290,000	£10,000	3%	£280,000
144	£595,000	£20,000	3%	£575,000
145	£750,000	£25,000	3%	£725,000
146	£300,000	£10,000	3%	£290,000
147	£160,000	£5,000	3%	£155,000
148	£650,000	£20,000	3%	£630,000
149	£325,000	£10,000	3%	£315,000
150	£500,000	£15,000	3%	£485,000
151	£500,000	£15,000	3%	£485,000
152	£350,000	£10,000	3%	£340,000
153	£540,000	£15,000	3%	£525,000
154	£360,000	£10,000	3%	£350,000
155	£241,500	£6,500	3%	£235,000
156	£385,000	£10,000	3%	£375,000
157	£215,000	£5,000	2%	£210,000
158	£435,000	£10,000	2%	£425,000
159	£220,000	£5,000	2%	£215,000
160	£700,000	£15,000	2%	£685,000
161	£240,000	£5,000	2%	£235,000
162	£249,995	£5,000	2%	£244,995
163	£800,000	£15,000	2%	£785,000
164	£650,000	£10,000	2%	£640,000
165	£415,000	£5,000	1%	£410,000
Report

Cell Formulas
Range	Formula
H2:H165	H2	=SUM(G2/F2)
I2:I165	I2	=[@[Old Price]]-[@Reduction]
M8	M8	=COUNTIF(Table1[%],"<5%")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I1:I165,G166:G1048576	Cell Value	=7500000	text	NO
I1:I165,G166:G1048576	Cell Value	=5000000	text	NO
I1:I165,G166:G1048576	Cell Value	=4000000	text	NO
I1:I165,G166:G1048576	Cell Value	=3000000	text	NO
I1:I165,G166:G1048576	Cell Value	=2500000	text	NO
I1:I165,G166:G1048576	Cell Value	=2000000	text	NO
I1:I165,G166:G1048576	Cell Value	=1750000	text	NO
I1:I165,G166:G1048576	Cell Value	=1500000	text	NO
I1:I165,G166:G1048576	Cell Value	=1250000	text	NO
I1:I165,G166:G1048576	Cell Value	=1000000	text	NO
I1:I165,G166:G1048576	Cell Value	=900000	text	NO
I1:I165,G166:G1048576	Cell Value	=800000	text	NO
I1:I165,G166:G1048576	Cell Value	=700000	text	NO
I1:I165,G166:G1048576	Cell Value	=650000	text	NO
I1:I165,G166:G1048576	Cell Value	=600000	text	NO
I1:I165,G166:G1048576	Cell Value	=550000	text	NO
I1:I165,G166:G1048576	Cell Value	=500000	text	NO
I1:I165,G166:G1048576	Cell Value	=475000	text	NO
I1:I165,G166:G1048576	Cell Value	=450000	text	NO
I1:I165,G166:G1048576	Cell Value	=425000	text	NO
I1:I165,G166:G1048576	Cell Value	=400000	text	NO
I1:I165,G166:G1048576	Cell Value	=375000	text	NO
I1:I165,G166:G1048576	Cell Value	=350000	text	NO
I1:I165,G166:G1048576	Cell Value	=325000	text	NO
I1:I165,G166:G1048576	Cell Value	=300000	text	NO
I1:I165,G166:G1048576	Cell Value	=290000	text	NO
I1:I165,G166:G1048576	Cell Value	=280000	text	NO
I1:I165,G166:G1048576	Cell Value	=270000	text	NO
I1:I165,G166:G1048576	Cell Value	=260000	text	NO
I2:I165	Expression	=$G:$G=$K$2:$K$30	text	NO

 

[Fluff]

Thanks for that.
In what way do you think it's not working? There are 54 values below 5% which is what the formula returns.

 

[Reecenorman1996]

Thanks for that.
In what way do you think it's not working? There are 54 values below 5% which is what the formula returns.

i have 43 that are 4%-1%

 

[Fluff]

If you expand the % column to show 2 decimal places you will see that everything from row 112 downwards is <5%

 

[Reecenorman1996]

oh my god , that didn't even cross my mind thank you so much and sorry for wasting your time