Solved

# Count blank rows between two values in a column

Posted on 2015-01-24
1,103 Views
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.
0
Question by:elimishia
• 4
• 3

LVL 81

Accepted Solution

byundt earned 500 total points
ID: 40568791
I realize from a Comment you made in another thread that the following formula is not working for you in cell E4:
=IFERROR(SUM(OFFSET(E5,0,0,MATCH("?*",A5:A\$100,0)-1)),0)

But since the formula is working in my test workbook, could you please put some data in it that breaks the formula? If you can, please post your revised copy of my workbook.
SumRowsBetweenTextValuesQ28603315.xlsx
0

LVL 81

Assisted Solution

byundt earned 500 total points
ID: 40568794
Note that my formula is testing for the presence of one or more characters in cells in column A. If your "blank" cells contain a space character, that will be detected as text.

I can ignore space characters by array-entering:
=IFERROR(SUM(OFFSET(E5,0,0,MATCH("?*",TRIM(A5:A\$100),0)-1)),0)

Array-entering means:
1. Select the cell, then click in the formula bar
2. Hold the Control and Shift keys down
3. Hit Enter, then release all three keys
Excel should respond by adding curly braces { } surrounding the formula.
0

Author Comment

ID: 40568801
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.
0

LVL 81

Assisted Solution

byundt earned 500 total points
ID: 40568804
Try using the Custom number format [h]:mm:ss in the cell with the formula.

If that doesn't work, please post my workbook along with some time data in column E that demonstrates the problem.

0

LVL 81

Assisted Solution

byundt earned 500 total points
ID: 40568805
Another possible issue is that your "times" in column E are actually text that looks like time. If so, you could array-enter the following:
=IFERROR(SUM(--OFFSET(E5,0,0,MATCH("?*",A5:A\$100,0)-1)),0)
SumRowsBetweenTextValuesQ28603315.xlsx
0

Author Comment

ID: 40568809
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.
0

Author Closing Comment

ID: 40568818
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.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…