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

 

[Matty]

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

Nice Aladin.

I went with COUNTIF in the initial formula I put together, but I'd forgotten about the ?* or *? (does it matter which way around you do it - I think not?) argument, hence it was abandoned for what I actually posted.

Matty

 

[Pinkyknip]

Hello Aladin,

Your formula seems to work as well with the exception that it also counts the header and the footer portion in the calculation. I did not want to include them, just wanted the information between them. A simple -2 on the end of the formula that you suggest will produce my desired results.

Matty,

To answer your question about the formula I *was* using, I was simply using in Sheet 2 an =Sheet1A1! type formula, but if Sheet1 cell A1 was blank your formula would still count it on Sheet 2. Changing the Formula to =IF(Sheet1!A1="","",Sheet1!A1) to force it to produce the "" has provided me with working results.

To both of you,

Again, your help is much appreciated. It's good people like you that help mediocre Excel users like myself look good at work!

Thanks again!!