asked on

Count blank rows between two values in a column

I would like to count the number of blank rows between two values in a column so that I can use that range in a SUM(). For example: A4 has a text value and A9 has a text value. The cells in between are blank. In column E I need to SUM(E5:E8). The values being added are time (minutes)

To further explain Col A has a number of cells with text, but the number of blank rows between the values varies. I would like to build a SUM() argument that automatically varies the range according to the number of blank rows. So what I am looking for is SUM(E&"The row below" : E&"The number of blank rows between the row I am on and the next cell in Col A that has a value"-1).

Hope this makes sense as I am not able to send the file. I am using Excel 2013, but the formula will also need to run on Excel 2010 on Windows 7 and 8 PCSs. Any help/ideas/suggestions wold be appreciated.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

elimishia

ASKER

Thanks. The blank cells don't have spaces. I think the problem is that in Col E I am adding up time (minutes). I can get your formula to work when I have normal numbers in the column, but it return 0:00 when I put in time.

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

elimishia

ASKER

Thank-you for the sample workbook. I copied your formula into my workbook and it worked - even using time. Interestingly, as soon as I copies your formula in, the other formulas that I have entered (which weren't working) then worked. I don't understand why that should be, but I am ever so happy to have the solution.

elimishia

ASKER

As a returning (previously very unhappy) Experts Exchange member, dealing with Byundt has been a very positive experience. The solution was exactly what I wanted, enhanced by the many additional suggestions when it didn't initially work (something I did). Added to that, the promptness of the help was amazing. Having an answer so quickly has directly effected productivity - positively. Thank-you.