Counting Between Two Cells

Pinkyknip

New Member
Joined
Aug 3, 2010
Messages
6
Example:

Cell A1 value = Tom

Cell A2 is empty

Cell A3 value = Mary

Cell A4 is empty

Cell A5 value = Steve

In B1 I'd like to count how many names appear BETWEEN "Tom" and "Steve" and disregard the empty cells. I would NOT want to include "Tom" or "Steve".

With this example the result should be the number "1" as "Mary" is the only name between "Tom" and "Steve" and the empty cells would not be counted.

I hope my question is clear.

Some background work I've done:

I've been able to find out how to count the number of cells between two cells with data with an =abs(match()) formula, but I cannot find a way to eliminate any blank cells from that formula.

I thank you in advance for any assistance!!!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

dodgerdawg

New Member
Joined
Jun 25, 2010
Messages
14
Use a COUNTA formula:

In B1 type this formula: =COUNTA(A2:A4). The answer will be '1'.
 

Pinkyknip

New Member
Joined
Aug 3, 2010
Messages
6
Hi Dodgerdawg,

Thank you for your assistance, but it's more complicated than that.

I need to specifically know how to count cells that contain a value between two other cells that contain a value.

I also need to add, there are formulas in the cells that are returning blank cells. This means they are not blank cells, but cells containing formulas referring to different cells that might be blank.

Example:

A1 value = HEADER

A2 value = formula reference to another cell which equals January

A3 value = formula reference to another cell which equals February

A4 value = formula reference to another cell which equals March

A5 value = formula reference to another cell which is blank

A6 value = formula reference to another cell which is blank

A7 value = formula reference to another cell which equals June

A8 value = FOOTER

I would like to count the number of months BETWEEN "Header" and "Footer". In this example the final result would be "4", since that is the number of months MINUS BLANKS between "Header and "Footer".

I hope this makes more sense.

Thanks again everyone for any assistance you may be able to provide.
 
Last edited:

Pinkyknip

New Member
Joined
Aug 3, 2010
Messages
6
Hi Dodgerdawg,

It's also more complicated than that.

I hope my most recent example has provided a little more of an idea of what I'm trying to accomplish.

From my research a simple COUNTA or COUNTIF will not give me a count between two different existing values in cells.

Thanks though!
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
One option:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 19px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Header</TD><TD></TD><TD>Count</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>January</TD><TD></TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>February</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>March</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>June</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Footer</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>{=SUM(IF(LEN(INDEX(A1:A8,MATCH("Header",A1:A8,0)+1):INDEX(A1:A8,MATCH("Footer",A1:A8,0)-1))>0,1))}</TD></TR><TR><TD>A5</TD><TD>=""</TD></TR><TR><TD>A6</TD><TD>=""</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>

Matty

PS Welcome to the Board!
 
Last edited:

Pinkyknip

New Member
Joined
Aug 3, 2010
Messages
6
Hi Matty,

Thank you for the warm welcome. The formula works great!

BUT

Please keep in mind that my 'blank cells' aren't actually blank, there are formulas in the cells which are referring to another blank cell in the workbook.

So the formula as it stands is counting the pre-existing formula. If I delete the formula in the cell the final result is correct. I'd like to have thie counting formula ignore formulas that result in a blank space.

Does this make sense?

Again, thank you so much, it looks like I'm almost there!!!

Thanks!!
 

Pinkyknip

New Member
Joined
Aug 3, 2010
Messages
6
I think I've come up with my own solution to my problem.

My formula was referring to another cell in the workbook that could be blank, but may not be. I simply did that with an =cellreference formula.

I've now made my pre-existing formula an "if" formula forcing it to provide a "" if the referenced cell is blank. Since it provides a "" in case of a blank cell the formula you provided will NOT count it and life is now good!

Again, one thousand and forty two thank you's for your help!
 

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Again, one thousand and forty two thank you's for your help!
No problem. Glad to help.

Out of interest, what was the formula you had in before your introduced the IF formula returning? Was it a simple 0? The formula I posted could have been adapted to account for that, but if you now have an acceptable solution, then that's great.

Matty
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Try...

=COUNTIF(INDEX(A:A,MATCH("header",A:A,0)):INDEX(A:A,MATCH("footer",A:A,0)),"?*")
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,020
Messages
5,508,854
Members
408,697
Latest member
Jay_Sks

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top