Counting Between Two Cells

[Pinkyknip]

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!!!

 

[dodgerdawg]

Use a COUNTA formula:

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

 

[Pinkyknip]

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.

 

[dodgerdawg]

How about COUNTIF?

In B1 type: =COUNTIF(A1:A5,"Mary").

 

[Pinkyknip]

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]

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!

 

[Pinkyknip]

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]

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]

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]

Try...

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