# Deriving column number for use within a (COUNTIF) formula

#### Stormy234

##### New Member
I would greatly appreciate some help with a method to derive a column number to be used as the range criteria for the COUNTIF function, where gthe column number is being derived in a lookup.

I have a two tables:
• Table A has 446 columns and 900 rows - the cells in row 1 of Table A contain column headings (as would normally be expected!), the cells in rows 2 to 900 contain eithers zero's or other (i.e. non-zero) values. No cells is Table A are blank.
• Table B is a transposed list of the column headings for Table A which has been sorted alphabetically, with some additional information about each Column heading - namely a) what column number that column heading is in Table A and b) how many non-zero values there are in that column in Table A.

I am trying top work out how to create a formula that will provide the second bit of information.

So for example,
Table A
 Incident Action Risk Id A 5 100 1 B 5 200 400 2 A 1 300

<tbody>
</tbody>

Table B
 ColTxt ColNo Formula Action 2 COUNTIF(B1:B4,"<>0") Id 4 COUNTIF(D1:D4,"<>0") Incident 1 COUNTIF(A1:A4,"<>0") Risk 3 COUNTIF(C1:C4,"<>0")

<tbody>
</tbody>

I need a generic formula for column 3 of Table B that will
1. use the text in ColumnA of Table B to determine which Column in Table A to use in the formlua (ie derive the Table A Column Number)
2. Having derived the column number is step 1, count how many non-zero values exist in that column in Table A
So I want a single formula to use in all rows of Table B Column 3. In the above example I have hardcoded the column to use in the range - I want to derive this so that a common formula can be used .

Thanks in advance and apologies for any confusion caused by my way of describing the problem - I hope it makes sense!

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### Oeldere

##### Well-known Member
C2=countif(INDIRECT(CHAR(64+\$B2)&2&":"&Char(64+\$B2)&5),"<>0")

and drag down

#### gsistek

##### Well-known Member
Using the sample data you posted I will go with the assumption that ColNum on TableB is in column ABC (totally arbitrary). Try the following:
=COUNTIF(OFFSET(\$A\$2:\$A\$5,,ABC2-1),"<>0") in cell ABD2 (Formula column). Suit to your needs.

Replies
1
Views
487
Replies
2
Views
64
Replies
1
Views
232
Replies
7
Views
176
Replies
7
Views
132

1,191,172
Messages
5,985,086
Members
439,940
Latest member

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