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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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
255
Replies
0
Views
223
Replies
3
Views
173

1,141,817
Messages
5,708,760
Members
421,588
Latest member
Wawie

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.

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