Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37

    Unanswered: Update Forumla (count)

    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. #2
    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.
    Inspiration Through Fermentation

  3. #3
    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. #4
    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).
    Inspiration Through Fermentation

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #6
    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
  •