Formula to refer to a column as number (Not VBA)

[gilsa]

Hi All,
I'd like to use the countif formula to count non blank cells in dynamic column.
I use MATCH to find the column number I need to find the cells in, however how do I refer to a column using its number?

i.e let's say my match function returns 2 (meaning column B), so I'd like to use: countif(B3:INDEX(B:B,$C$1),"<>"&"").
if it returns 4 then it will be D4 and D:D respectively.

Any idea/help would be highly appreciated!

 

[Andrew Poulsom]

I don't understand the D4 reference. But wouldn't it be?

INDEX(3:3,$C$1)

 

[XOR LX]

Hi.

Do you really need to restrict the number of rows being passed? COUNTIF(S)/SUMIF(S) are exceptional among Excel functions in that they suffer no detriment to performance if you pass entire columns.

So, you could use:

=COUNTIF(INDEX($A:$XFD,,match_result),"<>")

where obviously you replace match_result appropriately.

Regards

 

[gilsa]

Thanks for your prompt response!
3:3 refers to entire row 3, however I need to refer to Column 3 (C).
Also how do I pass my match result into the countif function?

 

[Andrew Poulsom]

If C1 contains 4 then

=INDEX(3:3,$C$1)

will return the value in D3, or a reference to that cell. So:

=SUM(B3:INDEX(3:3,$C$1))

will return the sum of B3:D3.