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

- 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)
- Having derived the column number is step 1, count how many non-zero values exist in that column in Table A

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