Hi,

If I have COUNT(A1:A100) with a total in the row #101 and when a user insert
a row between the 98 and 99 rows the function will be COUNT(A1:A101) and the
total will be in the row #102, which is fine - the row has been added into
the function.

But, when a user adding a row between the total row (101) and the previous
row (100), the total will be in the row 102 but the function will remain the
same and the one row will be missing from the calculation.

How could I resolve it and give the user the flexibility to add a row the
total row?

Thanks

Alex wrote:
Hi,

If I have COUNT(A1:A100) with a total in the row #101 and when a user insert
a row between the 98 and 99 rows the function will be COUNT(A1:A101) and the
total will be in the row #102, which is fine - the row has been added into
the function.

But, when a user adding a row between the total row (101) and the previous
row (100), the total will be in the row 102 but the function will remain the
same and the one row will be missing from the calculation.

How could I resolve it and give the user the flexibility to add a row the
total row?

Thanks

=COUNT($A$1:INDEX(A:A,ROW()-1))

Generally in that kind of scenario i will add a header row and a footer row
to straddle the range in question, and then the summation row goes below the
footer row and sums from header row to footer row inclusive. The header and
footer row are left empty and i will then shrink them to circa 1/4 size and
I will usually colour them to make the banding obvious. Then tell the user
that any row insertions MUST be done within the two bands.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Alex" wrote in message
...
Hi,

If I have COUNT(A1:A100) with a total in the row #101 and when a user
insert
a row between the 98 and 99 rows the function will be COUNT(A1:A101) and
the
total will be in the row #102, which is fine - the row has been added into
the function.

But, when a user adding a row between the total row (101) and the previous
row (100), the total will be in the row 102 but the function will remain
the
same and the one row will be missing from the calculation.

How could I resolve it and give the user the flexibility to add a row the
total row?

Thanks