Help in calculating # of columns dynamically

[Avi]

Hi,

Assume I have Y # cells of data in row X. (i am using x and y because
i am calculating it dynamically using Offset and Match)

I want to calculate Y in in the cell B9 with the formula
=COUNTA(MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6))
~ this does not work and returns the value 1

however if I put the Formula
=MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6) in Cell
C10 and the Formula
= Counta(Indirect (C10) in the Cell B10
~ it works fine and returns the # of columns in the matching row.

Can someone help me in finding, why it is happening, is there a way to
calculate the # of columns by using one formula only.

Thanks,
Avinash

 

[Vergel Adriano]

Avinash,

Try:

=COUNTA(INDIRECT(MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6)))

 

[Avi]

Yes, It did work.. How did I miss it

Avinash,

Try:

=COUNTA(INDIRECT(MATCH(Data!A9,Data!A1:A6) & ":" & MATCH(Data!A9,Data!A1:A6)))