• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNT rows until SUM value reached

S

steve Manos

New Member
Howdy Folks! 


I'm young to Excel, using XL 2010 with Windows 7, and was wondering if

there's a formula solution using COUNT and SUM in combination?


I used a helper column to get the answer to:

How many days does it take, before I clear $500 dollars?

The answer is 7.


I'd rather construct a simple formula though at the bottom of Column B ($/day)

to Count the number of rows, therfore days, until I reach my Sum value of $500.

[pre]
Code: 
Workd days     $/day    Helper column
1               61.21           61.21
2               81.90          143.11
3               83.66          226.77
4               70.83          297.60
5               91.28          388.88
6               87.06          475.94
7               74.73          550.67
8               82.34          633.01
9               85.11          718.12
[/pre]
Thanks,

Stevie!
 
Please see


http://chandoo.org/forums/topic/sum-column-until-condition-is-reached-return-number-of-cells-in-column-counted
 
I really like Kyle McGhee's solution from the link provided (modified for your range)


means you can just change the value in A1 to whatever target value you want (i.e. 500 or 400 etc)


Create 2 Names:

d - refers to: =Sheet1!$B$1:$B$9

dMMULT - refers to: =MMULT(--(ROW(d)>=TRANSPOSE(ROW(d))),d)


Then use this formula

=MATCH(TRUE,INDEX(dMMULT>A1,,),0)
 
Hi, I'm so glad I was able to find this forum as I have almost the same question. What I need is to return the Day when "B" reached 150.

I am using Hui's table as I find it really helpful.
Day Name Pay
1 A 61.21
1 B 81.9
2 B 83.66
2 A 70.83
3 A 91.28
3 B 87.06
4 B 74.73
4 A 82.34
Name: B
To Save: 150
Day =
2

Thank you in advance.

Sheri
 
Thank you Narayan. I prefer not to use a helper column as I already have a big file. I opened the file attached but the answer was different from what it should be. I attached a file as an example.
 

Attachments

  • Book1.xlsx
    9.1 KB · Views: 22
Hi Sheri,

I know you are looking for solution without helper column.

Attached file is with helper column used, but gets you the desired output. In case you may be interested.

Regards,
Prasad DN
 

Attachments

  • Book1.xlsx
    9.6 KB · Views: 66
This is great Prasad. Thank you so much. This gives me the output that I want, if only we can take out the helper column.
 
Hi everyone and thank you for all your help so far! :)
i am trying to do a similar thing (count rows(days) until a flow volume is reached), but the count restarts for each row. I would prefer for the subsum to be greater than the flow volume, but [match_type] -1 does not work (i think it is because the values are not ordered, but if i order the values, it changes the conditions and gives a wrong answer). o_O Not sure how to explain it better.

I tried two options(more details in the table)… but they don't work. Can you please help?

Thank you so much for trying.
 

Attachments

  • Outflows.xlsx
    17.8 KB · Views: 6
Hui said:
Steve

Assuming your table is in A1:B10 try:

=MATCH(TRUE,INDEX(SUMIF(OFFSET($B$1,1,,ROW(B1:B10)-ROW(B1)+1,1),"<>0")&<=500,0),0)


or see here

https://www.dropbox.com/s/ks7xb4ekbe8mfuq/Steve Pay Days.xlsx
Click to expand...

Hi Hui

I wonder if you can help me with a similar problem...

If you refer to the table below, I'd like to count the number of rows of Demand needed, from a specific date (D+1) and for a specific Product, to reach the value of Stock Available.

To explain my case better, I've included a column with the result that I'm looking to obtain for 3 different combinations of Product & Date.
If you could help me with this, I'd be extremely thankful!

Cheers
AlphaR

ProductDateDemand (CS)Stock Available (CS)Full Days of Supply
8885615/02/20204.1714.41
8885616/02/20204.869.55
8885617/02/20204.894.661 day
8885618/02/20202.711.95
8885619/02/20202.9319.02
8885620/02/20202.3116.71
8885621/02/20201.7314.98
8885915/02/202010.7327.124 days
8885916/02/20206.3120.81
8885917/02/20206.6914.12
8885918/02/20205.338.79
8885919/02/20205.1215.67
8885920/02/20204.6711
8885921/02/20204.696.31
8886115/02/202019.0767.125 days
8886116/02/202013.6553.47
8886117/02/202014.9538.52
8886118/02/202012.5425.98
8886119/02/202013.2112.77
8886120/02/202011.5981.18
8886121/02/202011.0570.13
 
vletm said:
AlphaR
Did You read Forum Rules? https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/
There are clear hints how to do these...
Please, reread and follow those hints.
Click to expand...
I'm sorry, I don't know what I've done wrong... Please accept my apologies! I didn't mean to upset anyone, I've just been struggling with this formula for a while and I thought that I could get some expert advise here. I'll re-read the forum rules again but if you could kindly point out what is that I should have done differently, that would be very much appreciated.
Thanks
 
AlphaR
As You could find from Forum Rules:

How to get the Best Results at Chandoo.org
  • Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
 
Hui said:
=MATCH(TRUE,INDEX(SUMIF(OFFSET($B$1,1,,ROW(B1:B10)-ROW(B1)+1,1),"<>0")&<=500,0),0)
Click to expand...
@Hui
I have seen OFFSET used to produce a sequence of values but I am struggling to determine the rules of the game!
Depending upon the way in which I have used OFFSET and SUMIF to build the 'cumulative pay' array, XMATCH may or may not need the INDEX. On the other hand XLOOKUP seems to work fine irrespective.
I haven't got to the bottom of this, so if you have any thoughts ...
Peter

Added notes. Calculating the cumulative pay (from a named formula)
= SUMIF(OFFSET(payHdr,1,,idx,1),"<>")
seemed to work all the time but splitting it into two steps
= OFFSET(payHdr,1, ,idx,1)
= SUMIF( priorPayments, "<>" )
was a little more hit or miss.
 

Attachments

  • Pay Days (PB).xlsx
    12.5 KB · Views: 10
Last edited:
Peter Bartholomew said:
@Hui
I have seen OFFSET used to produce a sequence of values but I am struggling to determine the rules of the game!
Depending upon the way in which I have used OFFSET and SUMIF to build the 'cumulative pay' array, XMATCH may or may not need the INDEX. On the other hand XLOOKUP seems to work fine irrespective.
I haven't got to the bottom of this, so if you have any thoughts ...
Peter

Added notes. Calculating the cumulative pay (from a named formula)
= SUMIF(OFFSET(payHdr,1,,idx,1),"<>")
seemed to work all the time but splitting it into two steps
= OFFSET(payHdr,1, ,idx,1)
= SUMIF( priorPayments, "<>" )
was a little more hit or miss.
Click to expand...

@Peter

A response to a post 7 years ago!. I mostly struggle to remember what I had for breakfast.
I will respond in a day or so, I'm a bit busy today
 
You must log in or register to reply here.
Back
Top