Count Rows in Excel Until a Certain Value Reached [2 Cases]

Counting rows with conditions is a common thing when working with data analysis in Excel. Today I will try to discuss this topic. Today’s condition is counting rows until a specific value is reached. I will try to dissect two different scenarios relating to counting rows until a certain value is met. First, I will discuss how to count rows until a target value is reached. Then I will discuss how to count rows until a specific value is reached in Excel.

Case #1: Count Rows in Excel Until a Target Value Reached

I have a weekly report of workdays vs daily sales. Now, I have set a Target Sales to meet.

Here, I will calculate the number of days required to hit the Target Sales.

I have taken a Target Sales amount of $300,000. The basic idea of counting the required days to hit the Target Sales is simple. Summing up each daily sales amount and counting each day by counting the number of rows required. When the sum of the daily sales becomes equal to the Target Sales, just return that row count number.

To calculate the number of days required to hit the Target Sales I have used the following formula:

=MATCH(F3,SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B8)-ROW(B2))),1)

Formula Explanation

• F3 is the Target Sales amount.
• B2 is the first cell of the weekly sales storage range.
• B2:B8 is the range of the weekly sales record.
• 9 is the reference number of the arithmetic SUM operation.

The formula returns 4. This means 4 workdays are required to hit the Target Sales amount of $300,000. The workdays are Sunday, Monday, Tuesday, and Wednesday.

🔗3 Ways to Find Column Index Number in Excel

🔗 2 Ways to Count Columns in Excel for Vlookup

Case #2: Count Rows in Excel Until Specific Value Reached

In the following scenario, I have picked up a daily sales amount from the weekly sales report. Now I will use a formula to return which no of days the sales amount was raised.

Here, I will use the XMATCH function to count rows until the picked sales amount matches one of the daily sales amounts.

The syntax of the XMATCH function:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

I used the following formula using the XMATCH function in cell F4.

=XMATCH(F3,B2:B8,,-1)

The formula returns 5. This means the 5th day of the weekly sales report has a sales amount of $58,789.

If you cross-check, you can see that the 5th day of the weekly sales report is Thursday. And Thursday has a daily sales amount of $58,789.

Formula Explanation

• F3 is the lookup_value which is a daily sales amount from the weekly sales report.
• B2:B8 is the range of the weekly sales amount.
• -1 is used to search from last to first.

🔗 [2 Cases] Count Columns in Excel Until a Certain Value Reached

🔗6 Ways to Count One Column in Excel If Another Column Meets Criteria

Conclusion

So, I have discussed 2 cases relating to counting rows in Excel until a specific value is reached. I expect you’ve found this article helpful. You can read more Excel articles from our website’s Blog page. Have a great workday!

🔗 3 Methods to Convert Column Number to Letter in Excel

🔗 [Excel VBA] Count Columns & Rows Having Data (10 Editable Codes)