Deriving column number for use within a (COUNTIF) formula

[Stormy234]

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!

 

[Oeldere]

C2=countif(INDIRECT(CHAR(64+$B2)&2&":"&Char(64+$B2)&5),"<>0")

and drag down

 

[gsistek]

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.