+ Reply to Thread
Results 1 to 7 of 7

Formula Index Column to find first blank cell and sum cells in different Column

  1. #1
    Forum Contributor
    Join Date
    09-25-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    151

    Formula Index Column to find first blank cell and sum cells in different Column

    I have attached a picture of an example for a formula I am hoping you can help me solve. I'm wanting the formula in G6 that looks starting at D7 (it offsets one row) and searches down D column until it finds the first blank cell (in the example it is D20). Now I want it to sum the values G7:G20. ANy suggestions?
    Thanks!
    Attached Images Attached Images

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,968

    Re: Formula Index Column to find first blank cell and sum cells in different Column

    Hi

    Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

    Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

    Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    09-25-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    151

    Re: Formula Index Column to find first blank cell and sum cells in different Column

    I have attached a file. Sorry...
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,624

    Re: Formula Index Column to find first blank cell and sum cells in different Column

    Try

    =SUM(OFFSET($G$7,,,ROW(OFFSET($D$7,COUNTA(D:D)-ROW($D$7),0))))

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,968

    Re: Formula Index Column to find first blank cell and sum cells in different Column

    Or, try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    09-25-2009
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    151

    Re: Formula Index Column to find first blank cell and sum cells in different Column

    Thanks, doesn't seem to work for me.

    I want the formula to start looking for the first blank cell at the D7 location. In my example, it would find the first blank at D19. Now I want it to give the sum from the range of G7:G19. Maybe I would have to use a macro?
    Thanks

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,352

    Re: Formula Index Column to find first blank cell and sum cells in different Column

    Quote Originally Posted by Hood View Post
    and searches down D column until it finds the first blank cell
    Try this:

    =SUM(INDIRECT("G7:G"&AGGREGATE(15,6,ROW(D7:D21)/(D7:D21=""),1)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 01-05-2017, 01:09 PM
  2. [SOLVED] Formula to find the last cell value in a column range that contains blank cells
    By Bonzopookie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-29-2014, 07:50 PM
  3. Find & select 1st blank cell in a range of formulated column cells
    By jnbroek in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-19-2013, 09:26 AM
  4. [SOLVED] Find first non-blank cell ABOVE formula cell in column. Possible without VBA?
    By RichardLynn in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-05-2013, 10:23 AM
  5. Replies: 7
    Last Post: 08-14-2013, 05:41 AM
  6. Find the value of the 2nd to last cell in a column that includes blank cells
    By LookIWashed4Supper in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-23-2013, 09:35 PM
  7. macro to find first blank cell in a column cut the value from left adjacent column
    By willykin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-01-2012, 09:23 AM

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