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.

Count Rows in Excel.xlsx

Table of Contents

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.

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.

Syntax

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

Formula

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

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.

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.

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!

Frequently Asked Questions

How do I conditionally count rows in Excel?

To conditionally count rows in Excel, use the COUNTIF function. Here’s a how:
Use the COUNTIF function in Excel to conditionally count rows based on a specified criteria. The formula is =COUNTIF(range, criteria). Replace ‘range’ with the range of cells and ‘criteria’ with the condition to be met. For example, to count rows where values are greater than 10 in column A, use =COUNTIF(A:A, “>10”)

How do you count the number of rows in Excel with value?

To count the number of rows in Excel with values, use the following formula: =COUNTA(A:A)
Replace “A” with the column letter that contains the values. This formula counts all non-empty cells in the specified column, effectively giving you the number of rows with values in that column.

What is the formula to count cells with text?

To count cells with text in Excel or Google Sheets, you can use the following formula: =COUNTIF(range, “<>”)
Replace “range” with the actual range of cells you want to count. This formula counts the number of cells in the specified range that are not empty. If a cell contains any text (even a single character), it will be included in the count.

Rate this post