Incrementing a cell selection

[Mil Browne]

How can I increment a cell selection in a formula? For example, I would like to count a group of cells, 30 at a time, going down a column.

ie if i have this formula: '=count(L2:L31)' and I want to skip down to the end of this range and count the next 30 cells, how do I do it?

I know that this will not work
'=count(L2+30:L31+30)'

Any assistance is much appreciated, thanks,

 

[Michael Bednarek]

How can I increment a cell selection in a formula? For example, I would like to count a group of cells, 30 at a time, going down a column.

ie if i have this formula: '=count(L2:L31)' and I want to skip down to the end of this range and count the next 30 cells, how do I do it?

I know that this will not work
'=count(L2+30:L31+30)'

Try: =Count(Indirect("L"&2+30&":L"&31+30))

 

[Mil Browne]

Thanks, it works perfectly. I have seen the 'indirect()'function before but never quite understood what it did, now I do.

 

[isabelle]

hi Mil,

place the formula on line 2 of the column of your choice and copy this formula down

=COUNTA(INDIRECT("L"&(ROW()*30)-28&":L"&(ROW()*30)+1))

--
isabelle



Le 2012-03-30 03:27, Mil Browne a écrit :