How to Count Rows After the Last Row Containing a Substring in Excel

Описание: Struggling to find the number of rows after the last occurrence of a substring in Excel? This guide provides a clear formula and in-depth explanation to help you manage your data efficiently.
---
This video is based on the question https://stackoverflow.com/q/66038875/ asked by the user 'No stupid questions' ( https://stackoverflow.com/u/5351630/ ) and on the answer https://stackoverflow.com/a/66039419/ provided by the user 'Axuary' ( https://stackoverflow.com/u/14385419/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Excel: Get number of rows after last row containing substring

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Count Rows After the Last Row Containing a Substring in Excel: A Step-by-Step Guide

Are you working with a dataset in Excel and need to track occurrences of specific terms, like "Legendary," to manage your calculations or counters? If so, you may have encountered the challenge of determining the number of rows that come after the last occurrence of a particular substring.

In this guide, we’ll walk through solving this problem step by step, using an example that illustrates the process clearly. This approach will ensure you have a precise value to reflect your counting needs, especially if you’re trying to create something like a pity counter for gaming items.

Understanding the Problem

Let’s consider a scenario where we have a list of items in Excel, and we want to find out how many entries occur after the last Legendary item. Here's our sample dataset:

[[See Video to Reveal this Text or Code Snippet]]

In the above table, let's break down our objective:

Identify the highest row index that contains the term "Legendary." In our sample, the last occurrence of "Legendary" is found in row 4.

Identify the last row index that contains any text. In our case, that would be row 6.

Calculate the difference – which tells us how many items are present after the last Legendary item (6 - 4 = 2).

The Solution Formula

To automate this counting process in Excel, you can use the following formula:

[[See Video to Reveal this Text or Code Snippet]]

Breaking Down the Formula

Let's understand how this formula works:

COUNTA(A:A): This part counts all non-empty cells in column A, providing the total number of items in your list.

FIND("(Legendary)", A:A): This function looks for the term "(Legendary)" in your data. If it doesn't find the substring in a cell, it will return an error.

ISERROR(...): This checks for any errors in the FIND function. If the term is not found, it returns TRUE; otherwise, it returns FALSE.

ROW(A:A): This returns the corresponding row numbers where the items are located.

MAX(...): Finally, the MAX function captures the highest row number that contains "Legendary." If there are no "Legendary" items found, it will ignore those rows due to the way ISERROR is applied.

Important Notes

Array Formula: If your version of Excel does not support dynamic arrays, make sure to enter this formula as an array formula. To do this, press Ctrl + Shift + Enter after typing the formula. You'll know it's an array formula if you see curly braces {} around it in the formula bar.

No Blanks: This formula assumes there are no empty cells in your list. If there are, the formula may need adjustment.

Conclusion

By following the steps and utilizing the provided formula, counting the number of rows after the last occurrence of a specified term such as "Legendary" in Excel can be easily managed. This handy technique comes in especially useful when working with extensive datasets or when you're trying to create counters based on item occurrences.

Feel free to replicate this process in your Excel sheets, and you’ll find that managing data can become a more efficient task. Happy Excel-ing!