Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 2062

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.
0
elimishia
• 4
• 3
4 Solutions

Commented:
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

Commented:
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 Commented:
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

Commented:
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

Commented:
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 Commented:
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 Commented:
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

• 4
• 3
Tackle projects and never again get stuck behind a technical roadblock.