Help in calculating # of columns dynamically
 

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

Help in calculating # of columns dynamically
 

Avinash,

Try:

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


--
Hope that helps.

Vergel Adriano


"Avi" wrote:

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

Help in calculating # of columns dynamically
 

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

On Aug 9, 5:30 pm, Vergel Adriano
wrote:
Avinash,

Try:

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

--
Hope that helps.

Vergel Adriano

"Avi" wrote:
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