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
  • Last Modified:

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
Asked:
elimishia
  • 4
  • 3
4 Solutions
 
byundtCommented:
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
 
byundtCommented:
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
 
elimishiaAuthor 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
byundtCommented:
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.

Brad
0
 
byundtCommented:
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
 
elimishiaAuthor 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
 
elimishiaAuthor 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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