Count between columns given a cell address

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
31
Office Version
  1. 2010
Platform
  1. Windows
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
ZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJ
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
574.5755374.5755274.5755274.5755374.5755413/03/2021 09:3113.713.610010000374.5755674.57556013.713.6038Blue$AD$238820/03/2021 15:3715.92.310£0.134-£0.13424
Sheet4
Cell Formulas
RangeFormula
AT2:AT5AT2=AJ2
AU2:AV5AU2=AF2
AW2:AW5AW2=SUM(AM2:AO2)
BJ2BJ2=COLUMNS(AZ2:AD)
AR5,AR2:AR3AR2=AQ2
AQ2:AQ5AQ2=SUM(AD2,U2,K2)/3
BF2:BF5BF2=ABS(BG2)
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
194
Office Version
  1. 365
Platform
  1. Windows
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
5$AD$23881
Sheet1
Cell Formulas
RangeFormula
BJ2:BJ5BJ2=COLUMNS(INDIRECT("AD:"&LEFT(AZ2,FIND("$",AZ2,2)-1),1))
 
Last edited:
Solution

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
31
Office Version
  1. 2010
Platform
  1. Windows
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
5$AD$23881
Sheet1
Cell Formulas
RangeFormula
BJ2:BJ5BJ2=COLUMNS(INDIRECT("AD:"&LEFT(AZ2,FIND("$",AZ2,2)-1),1))
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
Joined
Jul 30, 2020
Messages
194
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2017
Messages
31
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,704
Messages
5,707,970
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.
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