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