excel formula to counta based on header value

Question

I am trying to count the number of cells with content in them, the way COUNTA() does, based on the header value. This way I want a formula in a sheet that sees in this same sheet a reference, then look in another sheet another cell that matches - located in the first row, where the headers are - this reference and finally uses COUNTA() in the values below the header. I want the formula to look for the location of the column where I want the COUNTA() to work. I am not sure if I can do this without VBA, I was really hoping to find a formula that works.

Answer 1

Use INDEX() to return the correct range:

=COUNTA(INDEX(A:E,0,MATCH(G3,1:1,0)))-1

The -1 deals with the Header.

INDIRECT and OFFSET are volatile so use INDEX which is not.