Solved

Count blank rows between two values in a column

Posted on 2015-01-24
7
1,145 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 81

Accepted Solution

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

by:byundt
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

by:elimishia
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 81

Assisted Solution

by:byundt
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.

Brad
0
 
LVL 81

Assisted Solution

by:byundt
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

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

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Hiding information in Excel 16 70
MS Excel Vllokup or formula to see if a record exists... 1 22
Install both Office 2010 and 2013 4 34
conditional formatting 4 41
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
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 Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

816 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

10 Experts available now in Live!

Get 1:1 Help Now