How to find the target sum reached row by searching down an amount column in Google Sheets?

To answer this first I must explain what is the target sum in a column. Let me explain this concept with one example.

In a dataset (table), in Google Sheets, assume I have a date column A and an amount column B.

Column A and Column B are filled several rows down. For example purpose, we can consider a small range like A1:B15.

It’s the sales data from one of my employees and I had set him a sales target of $20,000. Again telling you it’s just an example 🙂

From summing the amount column from the top to bottom (B2:B15), and Lookup the date column, I can find on which date he has reached the given target.

We can find the target achieved date very easily if we could find the target sum reached row.

I hope I have well explained about the topic of this Google Sheets tutorial.

If you feel you require some more details/clarification on the topic, please see this example screenshot and the following explanation.

Explanation:

On 26/10/2019 (A11), the cumulative sales reached the target sum of $20,000.00 (E2). Because the total sales amount in the range B2:B11 is equal to $20,000.00.

To demonstrate/make you understand this, I have used the SUM function in cell D9. Please remember, it has nothing to do with our target sum reached row calculation.

The problem that I’m going to solve:

Count the number of rows starting from cell B2 until the sum of rows reaches the criterion amount in cell E2.

Once found the target amount reached row, return the date from column A.

Count Rows Until a Target Sum is Reached in Google Sheets

Let’s first write a formula to count rows until a target sum is reached in column B.

Finding the row of a target sum in a column is easy if you are ready to use a helper column. But I will definitely suggest you use a non-helper column-based formula.

In this tutorial, I’ve included both the helper column and non-helper column-based formulas.

With Helper Column – Exact Match

Begin with labeling cell C1 as “Cumulative Sum”, it’s optional though.

In cell C2, use this formula =B2. Then in cell C3 enter the below formula and drag down until you reach the cell C15.

=B3+C2

The above actions (formulas) generate a cumulative column C.

Now it is easy to count the rows until the target sum amount, i.e. $20,000.00, reached row.

Countif column C instead of column B to count until the target sum reached row.

=countif(C2:C15,"<="&F2)

This Countif formula counts the values in helper column C until the value reaches $20,000.00.

This way, using a helper column, you can easily find the row of the target sum reach in Google Sheets.

Without Helper Column – Exact Match

With the above helper column example, I have given you the idea of finding the row count of the amount column until a specific target (cumulative) sum value reached.

From the above example, you can easily understand that the key to finding the row with the target sum lies in the cumulative sum helper column.

I have used formulas in all the cells in the range C2:C15 to generate cumulative sums. There is an array formula to do that in one go.

Key the below array formula (expanding formula) in cell C2 after deleting all the existing non-array formulas in C2:C15.

=ArrayFormula(sumif(row(A2:A15),"<="&row(A2:A15),B2:B15))

Are you looking for this formula explanation?

Then please read this guide – Normal and Array-Based Running Total Formula in Google Sheets.

Now also we have a helper column! The only difference is the array formula in the helper column.

To avoid using the helper column do as follows. Instead of using the above formula in cell C2, directly use it within the Countif formula.

Replace the range C2:C15 in the Countif formula with this above cumulative sum (running sum) formula.

Formula 1:

=countif(ArrayFormula(sumif(row(A2:A15),"<="&row(A2:A15),B2:B15)),"<="&F2)

This way you can count rows until a specific sum reached.

What about if there is no exact match of the specific sum or target sum?

Actually this poses a real issue. Countif will count until, i.e. less than or equal to, the target sum value.

So if there is no exact match of the target sum value, the formula would return one row less as it counts until the nearest (lesser) value.

For example, if the value in cell F2 is $18,500.00, the formula would return the count 9. If you sum the first 9 rows (B2:B10), the total would be $18,200.00.

If you add one more row, the sum would be $20,000.00. So there is no exact match. If you want to return the count 10, not 9, there is one solution using Match. I’ll come to this later under the subtitle Formula When Target Sum Doesn’t Match.

Similar to Countif, we can use Match to count to the row containing the target sum in a cumulative column.

Count Until Target Sum Reached Row Using Match – Exact Match

Matching the target sum value in the cumulative sum column (helper column or cumulative sum array formula) will return the row count.

Generic Formula:

=match(target_sum_value,cumulative_sum,search_type)

Below is the equivalent formula to the Countif above.

Formula 2:

=match(F2,ArrayFormula(sumif(row(A2:A15),"<="&row(A2:A15),B2:B15)),0)

If there is no exact match, unlike the Countif, this formula would return #N/A.

This error we can make use as below.

Formula When Target Sum Doesn’t Match

Using IFNA we can return the row count of target sum + 1 in case of a mismatch (N/A error).

Syntax:

IFNA(value, [value_if_na_error])

value – match formula

value_if_error – match formula + 1

Formula 3:

=ifna(match(F2,ArrayFormula(sumif(row(A2:A15),"<="&row(A2:A15),B2:B15)),0),match(F2,ArrayFormula(sumif(row(A2:A15),"<="&row(A2:A15),B2:B15)),1)+1)

Lookup Target Sum Reached Row in Google Sheets

From the sales data, suppose you want to extract the date on which the specific target amount is achieved.

With the help of the Index function, we can find that easily as we have already the row offset, which is the above formula.

Index Syntax (for your quick reference):

INDEX(reference, [row], [column])

Here is that cool formula to Lookup the row of target sum in a column.

reference – A2:B15

row – The formula that returns the count of the target sum reached row. Use formula 1, formula 2, or formula 3. Here you can also use the cell reference F3 as we have already the count in that cell (see the image below).

column – 1

=index(A2:A15,F3,1)

Conclusion

I have explained;

1. How to find the target sum reached row in Google Sheets.
2. How to Lookup a row based on the cumulative sum (target sum) achieved.

Please do make the necessary changes in the formula as per your data range and locale settings.