- To post as a guest, your comment is unpublished.· 1 years agoHello, Diane, just change ROW to COLUMN in the formula like =MATCH(A2,SUBTOTAL(9,OFFSET(A1,,,COLUMN(A1:E1)-COLUMN(B2))),1). See screenshot:
- To post as a guest, your comment is unpublished.· 2 months agoThis doesn't work - I think it's because subtotal only works vertically?
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?
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
|Kutools for Excel's Auto Text, collect all formulas to a group as auto text, you can insert them anytime as you want whithout remembering! Free Download Now!|
You maybe interested in this utility:
|If there are some values which include both positive and negative values, how can you sum their absolute values up? Generally, you may change the negative values to positive first, and them sum them. But with Kutools for Excel's Sum absolute values, you can directly sum the absolute values as quickly as you can.|
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 1 years agohi , what can be the formula but to count columns?
- To post as a guest, your comment is unpublished.· 2 years agoAfter 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.)
- To post as a guest, your comment is unpublished.· 2 years agoNever mind. I figured out a simple, non array way to do what I wanted to do.