Let’s learn how to get the first and last row numbers of items in Google Sheets.
Suppose you want to find the total number of rows of an item. I mean the number of rows an item spread out in a column.
You can use the COUNTIF function for that.
Example:
=countif(B3:B14,"Apple")It may not be ideal in some cases. You may want to find the first and last row of an item first to get the count of rows.
The formula that you are going to learn in this tutorial will help you get the first and last row numbers of items in every group in Google Sheets.
By subtracting the first-row numbers from the last row numbers, you will get the total number of rows of every item.
Scenario 1:
Here in the above example, the items are in column B.
Scenario 2:
If the items repeat like in the below example, you may want to use a different formula to find the first and last row number of items in Google Sheets.
Note:- The COUNTIF that I have given as an example above applies to this dataset.
I have a few formulas in cells C3, D3, and E3. Below you are going to get those formulas and explanations.
Finding the First Row Numbers of Items in Every Group
Scenario # 1 and Scenario # 2 use two different methods (formulas) to find the first-row numbers of items.
To find the last row numbers, in both the scenarios, the formulas are the same.
In the first scenario, we can use the following array formula in cell C3 to get the first row numbers of the items in B3:B14.
=ArrayFormula(
if(
B3:B14<>"",
row(B3:B14),
)
)The formula returns row numbers, wherever the values are not blank in B3:B14 (please copy the example sheet from the link given at the end of this tutorial).
But in the second scenario, we should use a different formula, and here is that.
=ArrayFormula(
IFNA(
if(
{" ";B3:B14}<>{B3:B14;" "},
row(B3:B14),
)
)
)The changes are minimal, but the logic is entirely different. What’s the difference?
As per scenario 1, here we can’t test whether B3:B14 is blank or not because that doesn’t make sense with the data in hand.
Here instead the formula tests two virtual columns and they are {" ";B3:B14} and {B3:B14;" "}.
To make you understand, let me populate the said two arrays in columns C and D using the above two virtual column formulas.
I hope the above screenshot explanation makes sense.
Finding the Last Row Numbers of Items in Every Group
To find the last row numbers of items in every group, we can use the below Array Formula in cell D3. This formula is the same for scenarios 1 and 2.
=ArrayFormula(
ifna(
VLOOKUP(
C3:C14,
{lookup(row(C3:C14),if(len(C3:C14),row(C3:C14)),C3:C14),ROW(C3:C14)},
2,
1
)
)
)I know you may want a detailed explanation of the above formula, which returns the last row numbers of items in Google Sheets. So here you go!
Formula Explanation
Recently, I have published a rare piece of SUMIF tutorial. It was about how to use Sumif in Merged Cells in Google Sheets.
I have taken some of the parts of the above formula, especially the Vlookup ‘range’, from that tutorial.
To find the last row numbers of items in Google Sheets, I have used a Vlookup formula.
Vlookup Syntax: VLOOKUP(search_key, range, index, [is_sorted])
In my formula, the Vlookup arguments are as follows.
search_key: C3:C14 (the first row numbers of the items)
range: {lookup(row(C3:C14),if(len(C3:C14),row(C3:C14)),C3:C14),ROW(C3:C14)}
index: 2
is_sorted: 1
Here the Vlookup ‘range’ requires a special mention. So I am entering the range part of the formula in cell G3 to demonstrate its role.
The Vlookup ‘range’ is the result returned by a Lookup formula in one column (G3:G14) and the row numbers from 3 to 14 (H3:H14) in another column.
The Vlookup searches the search_key, i.e., C3:C14, in the first column in the ‘range’ and returns the max value from the second (index column number is 2 in the formula) column in the ‘range’.
Why does the formula return max values?
Because the last argument (is_sorted) in Vlookup is 1 and it means we are using a sorted ‘range’.
In a sorted range, the formula returns the nearest match (less than or equal to the search key).
In our case, there are multiple occurrences of search_keys in the first column in the ‘range’. The formula picks the last occurrences and returns values from the corresponding rows from the second column.
Lookup (Vlookup Range Part) Explained
lookup(row(C3:C14),if(len(C3:C14),row(C3:C14)),C3:C14)The Lookup formula returns the result in G3:H14 which can be split as below.
Syntax: LOOKUP(search_key, search_range|search_result_array, [result_range])
search_key: row(C3:C14)
search_range: if(len(C3:C14),row(C3:C14)) – If there are values in the cell range C3:C14, return the corresponding row numbers, else the Boolean value FALSE.
result_range: C3:C14
The Lookup searches the row numbers 3 to 14 in the above search_range and returns the values from the result range, which is the first-row numbers of every item in the group.
If search_key is not found in the search_range, the item used in the Lookup will be the value that’s immediately smaller in the range provided.
For example, the search key 4 is not in the search_range. So the immediate smaller value, which is 3, will be considered and the corresponding value from the result range will be returned.
See G2:G14 in image 4 for the Lookup result.
How to Get Total Rows in Every Group in Google Sheets
We have learned how to return the first row and last row of items in every group in Google Sheets.
Now to get total rows in every group, subtract the first-row number from the last row number and add 1.
You can use the below formula in cell E3 for that, and I think it doesn’t require any explanation.
=ArrayFormula(
if(
len(D3:D14),
D3:D14-C3:C14+1,
)
)Conclusion
Returning the first and last row numbers of items will be useful when grouping data. See images 1 and 2.
You can see that the items are grouped using the Data menu Group command.
When you collapse all the rows using my formulas, you can understand the number of rows in each group as well as the first and last row numbers of the items.
It may be useful in some specific cases.
That’s all. Thanks for the stay, enjoy!
