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

#### merskamp

##### New Member
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>

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Ron Coderre

##### MrExcel MVP
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
Code:
``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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
This is kind of a SWAG at what you might be looking for:
Code:
``````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

##### New Member
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:
Code:
``````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?

Replies
3
Views
238
Replies
4
Views
250
Replies
10
Views
784
Replies
3
Views
919
Replies
9
Views
599

1,191,133
Messages
5,984,856
Members
439,920
Latest member
mejatom

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back