This Google Sheets tutorial will help you learn how to write a formula to count rows (cells) between two specific values.

In a Google Sheets file, I have a few values in a column. Assume I know about two specific values in that column.

The cells between those two values may be empty or contain other values.

If so, how to count the rows (cells) between the said two values in Google Sheets?

You may please refer to column B (B3:B22) in the below image to get an idea about the sample data that I am referring to.

Needless to say, if our data is well organized like in database applications (eg. Microsoft Access), then such requirements may not come (I’m not going into that details).

But sometimes, we may require to keep our data in specific ways as above and do complex calculations.

In such a case, spreadsheets come as our first choice over database apps.

Let’s back to our problem. See there are two columns of data in B2:C.

First, I want to count the rows between the two values “Q2” and “Q3” in Google Sheets. How to do it?

Before coming to the solution to that problem you must understand one thing!

What’s that?

There must be some reason for such types of counting of rows/cells, right?

Here, in our case, that purpose is like this.

I want to find how many times the name “Jason” repeats between “Q2” and “Q3” or you can say during “Q2”.

The ultimate goal is to make a table that looks like the one in the range E3:I.

Let’s start writing the formula that can help us count the rows between two specific values in Google Sheets.

How to Count Rows between Two Values in Google Sheets

Since I’ve already given the sample data, I am going straightaway to write the required formula in step-by-step.

You can either use two cells to input the two values or hardcode them into the formula. First I am hardcoding the values or we can say criteria into the formula.

For the example purpose, assume, the said two values are “Q1” and “Q2”. Later you can change it to “Q2” and “Q3” or even “Q1” and “Q3”.

Finding Row Numbers of the “Two Values”

First, we must find the row numbers of the cell that contains the value “Q1” in column B. No doubt the function to use here is MATCH.

Formula_1

=MATCH("Q1",$B$1:$B,0)

To find the row number of “Q2” replace “Q1” with “Q2” in the above formula. Here is that Match formula.

Formula_2

=MATCH("Q2",$B$1:$B,0)

Actually, the MATCH function is to find the relative position. Since we have used the starting range in the Match formula from $B$1, the relative positions and row numbers would be the same.

So do note that even if your data starts from row#5 or any other row, use row#1 as the starting range in Match.

The first formula would return 3 whereas the second formula would return 10.

From this, we can find the number of rows between the two values “Q1” and “Q2” in Google Sheets. Here is how?

Formula to Count Rows between Two Values

It’s like 10-3-1. That’s;

formula_2-formula_1-1

Final_Formula

=MATCH("Q2",$B$1:$B,0)-MATCH("Q1",$B$1:$B,0)-1

The formula would return 6 (i.e. 10-3-1).

As you can see, the criteria have been hardcoded into the formula. Let’s make the formula flexible by referring those two criteria to corresponding cells.

In cell F3, enter “Q1” and in cell G3 enter “Q2” and use the below formula in cell F4.

=MATCH(G$3,$B$1:$B,0)-MATCH(F$3,$B$1:$B,0)-1

Change the criterion and see the changes in the result.

Add Additional Conditions to the Formula

You have learned the basic of counting rows between two values in Google Sheets. Now let’s use the formula in real life.

As you can see, I have one more column in my sample data, i.e. column C, that contain certain names. I want to include those names in the count.

Let’s conditional count the rows between two values in Google Sheets.

For example, I want to know how many times the name “Annie” repeats in “Q1” or we can say between the values “Q1” and “Q2”.

To conditional count cells/rows between two values, we can use the below logic in Google Sheets.

First, filter column C for rows that come between the rows 3 (“Q1”) and 10 (“Q2”). For that, we can use our formula_1 and formula_2 in a FILTER formula as below.

=FILTER(
     $C$1:$C,
     row($C$1:$C)>=match(F$3,$B$1:$B,0),
     row($C$1:$C)<=match(G$3,$B$1:$B,0)
)

It would return a range of cell names if there are enough blank rows (cells) to populate the result.

Use COUNTIF to count “Annie” in that range.

If the name “Annie” is in cell E4, then the formula would be like this.

=countif(
     filter(
        $C$1:$C,
        row($C$1:$C)>=match(F$3,$B$1:$B,0),
        row($C$1:$C)<=match(G$3,$B$1:$B,0)
     ),$E4
)

This way we can conditional count rows between two values in Google Sheets.

Output Table and One Issue

If our above formula is in cell F4, we can arrange the criteria as per F3:I3 (quarters) and E4:E6 (names).

Then drag the formula down until F6 and then the formulas in F4:F6 to the right until I6 to make a table as above.

Since the formulas use two values to filter the names in column C, the formulas may return 0s in Q4 (I4:I6). Because there is no second value to specify, i.e. “Q4” and…

So as a workaround, just tap space in cell B25 or down below that cell. Also, tap space in cell J3.

So the formula would treat it as Q4 and Space and subsequently you will get the total of Q4.

That’s all. Thanks for the stay. Enjoy.