• 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.

How to dynamically define pieces of a range

polarisking

polarisking

Member
Please see the attached file.

1702493817502.png

I have a range [Amount} defined as B2:B11. Whenever there's a "Y" in column C, Reset, I want the sum of all the Amount cells up to, and including, the row with the "Y". For each successive value of Y in Reset, I want the sum of Amount NOT INCLUDING the prior sum(s).

I hope this makes sense. Thank you for your attention. I'm thinking the solution will include OFFSET, ROW, and INDEX, but I can't wrap my head around this.
 

Attachments

  • Chandoo Example.xlsx
    15.4 KB · Views: 10
try in cell D2:
Code: 
=IF(C2="Y",SUM(INDEX($B$2:$B$11,IFERROR(XMATCH("Y",C1:C$2,0,-1)+1,1)):B2),"")
copied down. I'm sure there's a slicker way1
 
polarisking said:
Please see the attached file.

View attachment 85861

I have a range [Amount} defined as B2:B11. Whenever there's a "Y" in column C, Reset, I want the sum of all the Amount cells up to, and including, the row with the "Y". For each successive value of Y in Reset, I want the sum of Amount NOT INCLUDING the prior sum(s).

I hope this makes sense. Thank you for your attention. I'm thinking the solution will include OFFSET, ROW, and INDEX, but I can't wrap my head around this.
Click to expand...
polarisking said:
Please see the attached file.

View attachment 85861

I have a range [Amount} defined as B2:B11. Whenever there's a "Y" in column C, Reset, I want the sum of all the Amount cells up to, and including, the row with the "Y". For each successive value of Y in Reset, I want the sum of Amount NOT INCLUDING the prior sum(s).

I hope this makes sense. Thank you for your attention. I'm thinking the solution will include OFFSET, ROW, and INDEX, but I can't wrap my head around this.
Click to expand...
Try this!

Code: 
=IF(C2="Y",SUM($B$2:B2)-SUM($D$1:D1),"")
 
Something different!
Code: 
= LET(
    Addλ, LAMBDA(x,y, IF(ISNUMBER(y), x+y)),
    data, HSTACK(amount, reset),
    accum, SCAN(0, TOCOL(data,1), Addλ),
    compact, FILTER(accum, accum),
    IF(reset="Y", compact, "")
  )

1702514069841.png
 

Attachments

  • Partial Sums.xlsx
    17.1 KB · Views: 5
Thanks so much to all of you.

The formula that works, VLETM and Monty, is elegant in its simplicity: If the row is "asking" for a Reset Amount calculation, then subtract all prior Reset Amounts from the total of all Amounts. The final formula is

=IF(C2="Y",SUM($B$2:B2)-SUM($D$2:D1),"")

Peter Bartholomew and p45cal: your solutions introduced unique concepts that are very informative.
 
You must log in or register to reply here.
Back
Top