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

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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

ADVERTISEMENT

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

ADVERTISEMENT

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,203
Try...

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

Watch MrExcel Video

Forum statistics

Threads
1,132,823
Messages
5,655,475
Members
418,204
Latest member
ElizabethCorrin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top