## How to count rows until a certain sum value is reached in Excel?

For instance, there is a range of data, column A includes product number, and column B contains order amount, and you have a certain value such as 80, which is the total of first nth numbers in Order, how can you count how many rows are involved to sum up to 80 as below screenshot shown?

Count rows until a certain summed value is reached with formula

#### Count rows until a certain summed value is reached with formula

To count the number of rows which are used to sum up to a certain matched value, you can apply a formula.

Select a blank cell, E2 for instance, and enter this formula =MATCH(D2,SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B17)-ROW(B2))),1), press Shift + Ctrl + Enter key to get to the correct result. See screenshot:

Tip: In this formula, D2 is the certain value you want to reach by summing, B2:B17 is the values range you sum from, you can change them as you need.

If you want to exclude the header row, you can use this formula =MATCH(D2,SUBTOTAL(9,INDIRECT("\$B\$1"&":"&ADDRESS(ROW(\$B\$1:\$B\$17),COLUMN(\$B\$1)))), and press the  Shift +Ctrl + Enter keys simultaneously.

### Best Office Productivity Tools

Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel

 Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ... Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup .... Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List .... Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ... Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolset:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 30-day free trial.

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, how would the formula be if instead of D2 I want it to be an established range of data?
This comment was minimized by the moderator on the site
MATCH(D2,SUBTOTAL(9,OFFSET(B2,,,ROW(B2:B17)-ROW(B2))),1)this formula is not working on google sheets any info about that
This comment was minimized by the moderator on the site
Hi, Suresh, above formula only work in Microsoft Excel, if you want to count rows until a target sum reach in google sheet, you need a helper column, and the COUNTIF function. Specific operations as below screenshot shown. Please let me know if this help you.
This comment was minimized by the moderator on the site
hi , what can be the formula but to count columns?
This comment was minimized by the moderator on the site
Hello, Diane, just change ROW to COLUMN in the formula like =MATCH(A2,SUBTOTAL(9,OFFSET(A1,,,COLUMN(A1:E1)-COLUMN(B2))),1). See screenshot:
This comment was minimized by the moderator on the site
This doesn't work - I think it's because subtotal only works vertically?
This comment was minimized by the moderator on the site
After reading this solution, I started reading about array formulas. Very interesting and useful. With respect to this solution, it solves a very challenging aspect of material stockpile size and composition tracking. Can you help me with one little change...I want the position in the list of the total going from the bottom of the list up vs top down in your example. I've tried a few things but not cracked it yet, mainly due to not fully understanding the formula syntax yet. Any suggestions? Particularly interested in the exclude header version, or a version that does not include any header consideration since I wish to calculate this fresh on each row (i.e. day.)
This comment was minimized by the moderator on the site
Never mind. I figured out a simple, non array way to do what I wanted to do.
There are no comments posted here yet