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 Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
