# Count between columns given a cell address

#### Ed Harris

##### New Member
Hi
I have a column of cell addresses in AZ and I want to count the number of columns between those cell addresses and column AD.
I thought I could use Columns in BJ but can not see how to specify the cell contents over the cells own address.
Any help much appreciated.

test data 2021.xlsx
1DATA26DATA27DATA28DATA29DATA30TIMETEMPwindACCELEROMETERchart noaverageHRTempwindsum of accTEMPWINDHPEAKcolour
274.574674.574674.5746274.5747274.5747913/03/2021 09:1113.63.61001000074.5746374.57463013.63.6026Yellow\$AA\$600531/03/2021 14:5920.72.601£0.092-£0.09231#NAME?
374.5770974.5770974.5770674.5770474.5769313/03/2021 09:2313.76.210010000174.5770274.57702013.76.203Yellow\$AA\$95116/03/2021 06:1313.52.901£0.094£0.09432
474.5768174.576874.5768174.5767974.5768113/03/2021 09:2713.77.801010000274.5767474.57674013.77.8057Blue\$AC\$490028/03/2021 06:4514.76.210£0.038-£0.03823
Sheet4
Cell Formulas
RangeFormula
AT2:AT5AT2=AJ2
AU2:AV5AU2=AF2
AW2:AW5AW2=SUM(AM2:AO2)
AR5,AR2:AR3AR2=AQ2
BF2:BF5BF2=ABS(BG2)

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Habtest

##### Board Regular
Try this and you may adjust based on definition of "numbers of columns"
Book2
AZBJ
1
2\$AA\$60054
3\$AA\$9514
4\$AC\$49002
Sheet1
Cell Formulas
RangeFormula

Last edited:

#### Ed Harris

##### New Member
Try this and you may adjust based on definition of "numbers of columns"
Book2
AZBJ
1
2\$AA\$60054
3\$AA\$9514
4\$AC\$49002
Sheet1
Cell Formulas
RangeFormula
Yes, Brilliant that works. Many thanks. I adjusted by subtracting outside the formula. I can look up the functions but can you give me a very brief explanation how it works?

#### Habtest

##### Board Regular
Take \$AA\$6005 as an example
FIND() finds "\$" sign positin starting from the second character in cell AZ2, and returns 4, where the row number "\$6005" begins.
LEFT() returns the string before "\$6005", which is the column number "\$AA";
What we need is essentially COLUMNS(AD:\$AA), and nesting everything above inside INDIRECT() will do just that.

#### Ed Harris

##### New Member
Take \$AA\$6005 as an example
FIND() finds "\$" sign positin starting from the second character in cell AZ2, and returns 4, where the row number "\$6005" begins.
LEFT() returns the string before "\$6005", which is the column number "\$AA";
What we need is essentially COLUMNS(AD:\$AA), and nesting everything above inside INDIRECT() will do just that.
Many thanks.

Replies
12
Views
162
Replies
92
Views
1K
Replies
9
Views
251
Replies
0
Views
222
Replies
3
Views
172

1,141,704
Messages
5,707,966
Members
421,538
Latest member
Krisco

### 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