Solved

Count blank rows between two values in a column

Posted on 2015-01-24
7
1,022 Views
Last Modified: 2015-01-24
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
Comment
Question by:elimishia
  • 4
  • 3
7 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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 80

Assisted Solution

by:byundt
byundt earned 500 total points
Comment Utility
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

by:elimishia
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 80

Assisted Solution

by:byundt
byundt earned 500 total points
Comment Utility
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
 
LVL 80

Assisted Solution

by:byundt
byundt earned 500 total points
Comment Utility
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

by:elimishia
Comment Utility
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

by:elimishia
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now