1. Registered User
Join Date
Feb 2007
Location
Devon, UK
Posts
37

ok this is probably a very simple answer, at the moment i have a small spreadsheet, very basic and all i need is simple. I have about 20 rows, which i am using a COUNT function on to pull back the number of rows, so that that figure can be multiplied by a value to produce a cost, say 27 rows @ 60p pounds for example.

Now i want my count function to auto update when i insert a new row into the sheet, so that it would automatically change the number of rows from say 20 to 21 when a new row has been input.
Is it possible to do and im just an idiot or is there another explanation

2. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
You could perform the count over the whole column
=COUNT(A:A)
would only count non blank cells.

3. Registered User
Join Date
Feb 2007
Location
Devon, UK
Posts
37
ok i didnt really explain it well, this is a small section of a bigger sheet, there are other sections above and below this one that use data in the same column so i cant use that suggestion although thankyou for trying, is there any other thing i can use??

4. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
I didn't think you'd be able to get away with that, but you did say it was a "small" spreadsheet.

If you insert the row anywhere in the middle of the range, rather than adding a row to the bottom (or top), it will automatically expand the range in your count function. So if you count your range + 1 extra row, it would be tranparent to your user (but not really foolproof).

5. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Hi

I think it is possible to do what you want

DO you want to count all rows

then try this

=COUNTA(B1:B7)+COUNTBLANK(B1:B7)

non blank cell rows, try this

=COUNT(B1:B7)+COUNTBLANK(B1:B7)

Numeric data cell rows only, then this

=COUNT(B1:B7)

???

MTB

6. Registered User
Join Date
Feb 2007
Location
Devon, UK
Posts
37
when i get home this evening i will post a copy of the file, i only want to select the actual records and count them, at the moment they run from say cell 214 to 255, each month, some rows in the middle would get deleted and new ones are added on the end, just want the count to auto update the figure when these changes all happen, its more a timesaving excercise than anything else.

I gotta sort out the file before i post it as some of the data may be sensitive thats all.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•