+ Reply to Thread
Results 1 to 6 of 6

How Do You Make A Formula Stop At A Blank Cell?

  1. #1
    Registered User
    Join Date
    01-09-2007
    Posts
    2

    Cool How Do You Make A Formula Stop At A Blank Cell?

    HOW DO YOU MAKE A FORMULA STOP AT A BLANK CELL? For example, if you have data in a single column with a blank row separating each data set, how can you have a sum displayed in the separating space if the number of rows between each space is different? Obviously I'm looking for a fuction to automate the task to avoid manual processing.
    Thanks!

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    Does this help

    If you select the range plus 1 x blank cell then press F5 and select Blank cells you can then click on the sum function

    E.g If A1 to A100 is your range then select A1 to A101

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Summing at a blank cell

    Very Cool Noob! Thanks

    ChemistB

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Glad you found it useful

    VBA Noob

  5. #5
    Registered User
    Join Date
    01-09-2007
    Posts
    2
    Thanks for the reply. I'll try to be more specific.
    I am analyzing sales information for the past year for about 2000 inventory items.
    In column "A" I have product part numbers. There is a list of invoice numbers in column "B". There are many Invoice numbers per part number, one number for each time the part was sold over the last year. Column "C" shows the Quantity sold for each invoice.
    I want to find the total quantity sold for each part number by adding up the quantity sold information per part number. The problem is that each part was sold a different number of times so my "Sum" range is inconsistant.
    There is a blank row separating each block of invoice/quantity numbers, so I thought I could have the formula somehow detect this.
    Check out the attachment for a simplified example.
    Any thoughts?
    Attached Files Attached Files

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    Autofilter the range. Column C filter on Blanks then Column A filter on blanks then Select Column F5 > Special > blanks and then autoSum

    You could record a simple macro to do this


    VBA Noob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1