How to add a number of cells together when that number changes for each row.

[merskamp]

Tough to explain this:
I need to calculate a value for cell J8 in the table below.

I8 is the first cell containing a W in several cells and J8 needs to add up all
the negative values in J up until the last row with a W (which is column 1).

So it needs to add up J2 through J7. How can I do this by formula?
I need to do this for 500 rows and sometimes there are 4 L's in a row, sometimes 6
such as in this example.... can Excel do this?

Thanks,
Simon
<TABLE BORDER><TR>
<TH> </TH> <TH>H</TH> <TH>I</TH> <TH>J</TH>
</TR>


<TR><TD WIDTH=100>1</TD><TD WIDTH=100>-1.13</TD> <TD WIDTH=100>W</TD> <TD WIDTH>5</TD></TR>

<TR><TD WIDTH=100>2</TD><TD WIDTH=100>-1.17</TD> <TD WIDTH>L</TD> <TD WIDTH>-5.85</TD></TR>

<TR><TD WIDTH=100>3</TD><TD WIDTH=100>-1.17</TD> <TD WIDTH>L</TD> <TD WIDTH>-11.7</TD></TR>

<TR><TD WIDTH=100>4</TD><TD WIDTH=100>-1.08</TD> <TD WIDTH>L</TD> <TD WIDTH>-22.95</TD></TR>

<TR><TD WIDTH=100>5</TD><TD WIDTH=100>-1.18</TD> <TD WIDTH>L</TD> <TD WIDTH>-46.4</TD></TR>

<TR><TD WIDTH=100>6</TD><TD WIDTH=100>-1.01</TD> <TD WIDTH>L</TD> <TD WIDTH>-91.95</TD></TR>

<TR><TD WIDTH=100>7</TD><TD WIDTH=100>-1.25</TD> <TD WIDTH>L</TD> <TD WIDTH>-185.1</TD></TR>

<TR><TD WIDTH=100>8</TD><TD WIDTH=100>-1.08</TD> <TD WIDTH>W</TD> <TD WIDTH> </TD></TR>

<TR><TD WIDTH=100>9</TD><TD WIDTH=100>-1.14</TD> <TD WIDTH>W</TD> <TD WIDTH> </TD></TR>


</TABLE>

 

[Ron Coderre]

Using your posted data...

This formula, in J8, returns the sum of Col_J values
from J7 back up through the Col_J cell in the row of
the last previous occurrence of "W" in Col_I

J8: =SUM(INDEX(C:C,MAX(INDEX((C2:C8="W")*ROW(C2:C8),0))):D8)

In the example, the formula sums from J2:J7

Is that something you can work with?

 

[merskamp]

Not quite Ron.
I am assuming in your formula there i change all the C's to an I?
When I plug it in then into J8, (using I for C), its returning a value of 0.

Also , I need to put this formula in every cell going down as part of an If then.
Every so often based on the If/Then it will need to calculate up
either 4 or 6 or 2 cells. Your one looks like it hardcodes the 6 rows but I will
need that to be flexible if that makes any sense...

 

[Ron Coderre]

I was(am?) a bit puzzled by the contents of J1:J7.
Where did those values come from?

If they are pre-populated, do you then want formulas in
J8 down through J-whatever that always sum from the
previous "W" through the cell immediately above
the current cell?

 

[merskamp]

Hi Ron
Fair point
I threw that in as dummy data. (everything above J8)

THe way it works is I am adding data line by line, one line per day and the values above effect what goes in on that day.

So J8 is entered on day 8 and if its a Win day (as determined in column I), then I need to find the last win and tally all the losses since.
So I go up and see the last Win was in cell I1, now I need to count J2-J7 (the losses) and place that value in J8.

Suppose I get to Day 15, and find there were only 2 losses though? on days 13 and 14.
THen I'd need J15 to tally the values of J14 and J13 but thats all. Two values in this example , not 6.

Ideally some sort of formula that I can populate with a Fill would be perfect.

Simon

 

[Ron Coderre]

This is kind of a SWAG at what you might be looking for:

J8: =IF(AND(I8="W",I7="L"),SUM(INDEX(J:J,LOOKUP(2,1/(I$1:I7="W"),
ROW(I$1:I7)+1)):J7),"n/a")

Copy that formula down as far as you need.
When Row_8 data becomes available, it will be entered in H8, I8, and J8.

Am I on the right track?

 

[merskamp]

Hi Ron this seems to be working for me (sorry i in a course yesterday afternoon so apologies for late late response) - i have plenty to work with here and build into another rule. Your formula seems right on.

Cheers,
Simon

This is kind of a SWAG at what you might be looking for:

J8: =IF(AND(I8="W",I7="L"),SUM(INDEX(J:J,LOOKUP(2,1/(I$1:I7="W"),
ROW(I$1:I7)+1)):J7),"n/a")

Copy that formula down as far as you need.
When Row_8 data becomes available, it will be entered in H8, I8, and J8.

Am I on the right track?