1

I am wondering if someone can point me in the right direction regarding a problem I am trying to solve in Excel.

I would like to have a cell count the number of cells within a range that contain any data. So.. COUNTA.

However, I would like this count to update dynamically when rows are inserted into the range (making the range larger).

Right now I can do this by using

=COUNTA(INDIRECT("C7:C" &ROW()-1))

If I insert a ROW after C7 and above the row where my formula is, the count will go up.

My problem is that I would like to use this formula for multiple separate ranges in the same column.

If I have

=COUNTA(INDIRECT("C7:C" &ROW()-1)) 

and

=COUNTA(INDIRECT("C12:C" &ROW()-1)) 

working in my sheet, the second formulas count becomes inaccurate when a row is inserted into the first "range".

Hopefully that makes sense. Any help would be appreciated.

Thanks,

1

Try:

=COUNTA(INDIRECT("C"&ROW($C$7)&":C" &ROW()-1))

This will allow the referenced cell to change as rows are inserted.

See also: Increase the Row referenced in INDIRECT when dragging a formula down

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.