# Formula to Count number of cells in a column until a criteria (or value) is reached

##### New Member
Hi all,
I am looking for a way to count the number of cells in a column until a specific value*(1+X%) is reached, then start the count again in a different cell.
In the mini sheet I've uploaded:

The formula should be placed in C2 and use array A3:A54 to count cells downwards until it encounters a value which is equal or higer than B3*(1+1%). The result on this cell with the furmula in this case would be 15 (counts from row A3 to A17 marked in red)
I would like to be able to drag the formula downards, so, for instance, once it reaches cell C30, it will do the same thing (only this time, the array will be A31:A54 which is covered by dragging the formula downards). In this case the result of the formula on cell C30 would be 1, since A31>B30*(1+1%)

Any ideas?

Thank you!

Example.xlsx
ABC
11.240
211.11615
31.010
41.020
51.050
61.070
71.080
81.080
91.060
101.050
111.020
121.020
1310
140.930
150.860
160.860
171.130
181.180
191.180
201.190
211.190
221.170
231.190
241.250
251.260
261.230
271.240
281.210
291.190
301.161.0441
311.190
321.210
331.20
341.190
351.180
361.20
371.180
381.170
391.120
401.130
411.080
421.050
431.020
441.040
451.020
461.040
471.020
481.020
491.020
501.10
511.090
521.030
531.040
541.060
Sheet1

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
+Fluff 1.xlsm
ABC
11.240
211.11615
31.010
41.020
51.050
61.070
71.080
81.080
91.060
101.050
111.020
121.020
1310
140.930
150.860
160.860
171.130
181.180
191.180
201.190
211.190
221.170
231.190
241.250
251.260
261.230
271.240
281.210
291.190
301.161.0441
311.190
321.210
331.20
341.190
351.180
361.20
371.180
381.170
391.120
401.130
411.080
421.050
431.020
441.040
451.020
461.040
471.020
481.020
491.020
501.10
511.090
521.030
531.040
541.060
Master
Cell Formulas
RangeFormula
C2:C53C2=IF(B2>0,MATCH(1,--(B2*1.01<=A3:A\$54),0),"")
C54C54=IF(B54>0,MATCH(1,--(B54*1.01<=A\$54:A55),0),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

##### New Member
Like magic! Thank you so much Fluff, much appreciated!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
0
Views
75
Replies
5
Views
303
Replies
8
Views
525
Replies
1
Views
90
Replies
1
Views
134

1,141,406
Messages
5,706,268
Members
421,437
Latest member
GijoeBlack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back