We help IT Professionals succeed at work.

Help creating a dynamic "CountA" function without VBA (if possible)

Attached is spreadsheet that performs a number of counts for a given table.  I would like to make this function a little bit more dynamic so that if any rows are inserted or deleted, the count will automatically adjust without having to worry about it the end user manually re-adjusted the range in the formula.

If possible, I'd like to accomplish this without VBA.  Thanks for any suggestions.
COUNTA-Function.xlsx
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
It might depend on how you expect to change the tables but this should work....

In B1

=COUNTA(B2:INDEX(B2:B$1000,MATCH(TRUE,INDEX(B2:B$1000="",0),0)))

then in B2 copied down

=IF(B1="",COUNTA(B3:INDEX(B2:B$1001,MATCH(TRUE,INDEX(B2:B$1001="",0),0))),"")

regards, barry
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you use the Insert...Table feature on the ribbon, then you can use a formula like:
=COUNTA(Table1)

The table will automatically grow and shrink as you add or subtract data, and the COUNTA will update accordingly.

Author

Commented:
I'm going to go with Barry's solution on this.  Thanks for the quick responses! ;-)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.