Count Rows in a Dynamic Range

joseulloa22

New Member
Joined
Dec 28, 2013
Messages
22
Hello- good morning
Looking for help. The below table can start in any row (i.e, cell A17= "Components" could be in A150). Columns will remain as it shows . In column I (starting in I22 in this case), I would like to have the content of B22. On this case, I used the following formula but since my # of rows will change I think that rather than using A21 I would need a dynamic reference but not sure how to do that

=OFFSET(INDEX(A:A,MATCH("Components",A:A,0)),ROWS(INDEX(A:A,MATCH("Components",A:A,0)):A21),1)
Capture.PNG
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
558
Office Version
  1. 365
Platform
  1. Windows
Is it always 5 rows between components and the item? (i.e. row 17 + 5 = row 22) If so +5 could replace :A21
 

joseulloa22

New Member
Joined
Dec 28, 2013
Messages
22
Thanks. Yes, it is always 5 rows between Components and the first item . I added +5 in place of :A21 but it worked well for the first row (I22 in this case) but then when dragging down the formula it is pulling the same result. I guess in I22 it should add 5 as you said, then in I23 add 6, i24 add 7, etc. but not sure how to do this dynamically
 

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
558
Office Version
  1. 365
Platform
  1. Windows
You want col I to equal col B for all entries?
 

joseulloa22

New Member
Joined
Dec 28, 2013
Messages
22

ADVERTISEMENT

Yes, please. That's the output I am looking for.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows
Book1
ABCDEFGHIJKLMNOPQ
17Component
18ShortData1abcde
19Data1abcde
20IdentificationData1abcde
21---------------Data1abcde
22Data1abcdefData1Data1abcde
23Data1abcdefData1abcde
24Data1abcdefData1abcde
25Data1abcdefData1abcde
26Data1abcdefData1abcde
27Data1abcdefData1abcde
28Data1abcdefData1abcde
29Data1abcdefData1abcde
30Data1abcdefData1abcde
31Data1abcdefData1abcde
32Data1abcdefData1abcde
33Data1abcdefData1abcde
34Data1abcdefData1abcde
35Data1abcdefData1abcde
36Data1abcdefData1abcde
37Data1abcdefData1abcde
38Data1abcdefData1abcde
39Data1abcdefData1abcde
40Data1abcdefData1abcde
41Data1abcdefData1abcde
42Data1abcdefData1abcde
43Data1abcdefData1abcde
44Data1abcdefData1abcde
45Data1abcdefData1abcde
46Data1abcdefData1abcde
47Data1abcdefData1abcde
48Data1abcdefData1abcde
49Data1abcdefData1abcde
50Data1abcdefData1abcde
51Data1abcdefData1abcde
52Data1abcdefData1abcde
53Data1abcdefData1abcde
54Data1abcdefData1abcde
55Data1abcdefData1abcde
56Data1abcdefData1abcde
57Data1abcdefData1abcde
58Data1abcdefData1abcde
59Data1abcdefData1abcde
60Data1abcdefData1abcde
61Data1abcdefData1abcde
62Data1abcdefData1abcde
63Data1abcdefData1abcde
64Data1abcdefData1abcde
65Data1abcdefData1abcde
66Data1abcdefData1abcde
67Data1abcdefData1abcde
68Data1abcdefData1abcde
69Data1abcdefData1abcde
70Data1abcdefData1abcde
71Data1abcdefData1abcde
72Data1abcdefData1abcde
73Data1abcdefData1abcde
74Data1abcdefData1abcde
75Data1abcdefData1abcde
76Data1abcdefData1abcde
77Data1abcdefData1abcde
78Data1abcdefData1abcde
79Data1abcdefData1abcde
80Data1abcdefData1abcde
81Data1abcdefData1abcde
82Data1abcdefData1abcde
83Data1abcdefData1abcde
84Data1abcdefData1abcde
85Data1abcdefData1abcde
86Data1abcdefData1abcde
87Data1abcdefData1abcde
88Data1abcdefData1abcde
89Data1abcdefData1abcde
90Data1abcdefData1abcde
91Data1abcdefData1abcde
92Data1abcdefData1abcde
93Data1abcdefData1abcde
94Data1abcdefData1abcde
95Data1abcdefData1abcde
96Data1abcdefData1abcde
97Data1abcdefData1abcde
98Data1abcdefData1abcde
99Data1abcdefData1abcde
100Data1abcdefData1abcde
101Data1abcdefData1abcde
102Data1abcdefData1abcde
103Data1abcdefData1abcde
104Data1abcdefData1abcde
105Data1abcdefData1abcde
106Data1abcdefData1abcde
107Data1abcdefData1abcde
108Data1abcdefData1abcde
109Data1abcdefData1abcde
110Data1abcdefData1abcde
111Data1abcdefData1abcde
112Data1abcdefData1abcde
113Data1abcdefData1abcde
114Data1abcdefData1abcde
115Data1abcdefData1abcde
116Data1abcdefData1abcde
117Data1abcdefData1abcde
118Data1abcdefData1abcde
119Data1abcdefData1abcde
120Data1abcdefData1abcde
121Data1abcdefData1abcde
122Data1abcdefData1abcde
123Data1abcdefData1abcde
124Data1abcdef
125Data1abcdef
126Data1abcdef
127Data1abcdef
128Data1abcdef
129
130
131
Sheet3
Cell Formulas
RangeFormula
L18:Q123L18=OFFSET(INDEX(A:A,MATCH("Component",A:A,0)),5,1,MATCH(REPT("Z",255),B:B)-MATCH(REPT("Z",255),A:A)-5,6)
Dynamic array formulas.
#VALUE!
 

joseulloa22

New Member
Joined
Dec 28, 2013
Messages
22

ADVERTISEMENT

Thanks CA_Punit. I am getting an error when entering in L18 the suggested formula
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows
Enter With Control+Shift+Enter and Notr Just Enter and Drag the Formula Down
 

joseulloa22

New Member
Joined
Dec 28, 2013
Messages
22
Thanks CA_Punit. Did it as indicated. I see I am getting the same result as I drag down the formula. Please, remember column I should equals column B for all entries. In my example above, I22 should equals B22, I23 should equals B23, I24=B24, etc.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
900
Office Version
  1. 365
Platform
  1. Windows
Can you post a sample using Xl2BB Addin available in the Right Hand Side of the reply Box

Download and run the file
If not able to do it then please post your data through Drop Box
 

Watch MrExcel Video

Forum statistics

Threads
1,130,181
Messages
5,640,640
Members
417,159
Latest member
Mayozero

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