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

dadaddydude

New Member
Joined
Apr 4, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+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.
 
Solution

dadaddydude

New Member
Joined
Apr 4, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Like magic! Thank you so much Fluff, much appreciated!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top