Sum last 13 cells in column
 

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks

Sum last 13 cells in column
 

Tr this

=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000)

This an array which must be entered using CTRL+Shift+Enter and not just enter.
If you do it correctly then Excel will put curly brackets around the formula
{}. You can't type these yourself..

Mike

"vdubluv74" wrote:

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks

Sum last 13 cells in column
 

That worked perfect, but I messed up on what i needed. so here is the updated
version

find the last entry in a cell and calculate the previous 13 cells. the
previous cells may be blank. because i have fomulas in the other cells i
would need something like this.

if (the last cell in column I is greater than 0) then (take the last cell in
column J, next to column I, so if the last cell in I is I10 the formula would
use J10. and add the number in the previous 13 cells in that column)

wow

"Mike H" wrote:

Tr this

=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000)

This an array which must be entered using CTRL+Shift+Enter and not just enter.
If you do it correctly then Excel will put curly brackets around the formula
{}. You can't type these yourself..

Mike

"vdubluv74" wrote:

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks

Sum last 13 cells in column
 

Hmm,

I suspect were into VB now. Let us assume these are the last cells in
columns I & J. What result do you expect and why?

1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 11
12
10 13
11 14
12 15
14 16
15 17
0 18

Mike

"vdubluv74" wrote:

That worked perfect, but I messed up on what i needed. so here is the updated
version

find the last entry in a cell and calculate the previous 13 cells. the
previous cells may be blank. because i have fomulas in the other cells i
would need something like this.

if (the last cell in column I is greater than 0) then (take the last cell in
column J, next to column I, so if the last cell in I is I10 the formula would
use J10. and add the number in the previous 13 cells in that column)

wow

"Mike H" wrote:

Tr this

=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000)

This an array which must be entered using CTRL+Shift+Enter and not just enter.
If you do it correctly then Excel will put curly brackets around the formula
{}. You can't type these yourself..

Mike

"vdubluv74" wrote:

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks

Sum last 13 cells in column
 

Hi, I need a formula, working with excel 2003, and very new to it.

so with those being the last cells this is what I would need the answers to
be

107 144

those are the totals from the 3rd row to the bottom. but if you had 1s in
the next column and then a formula after the last cells i would need 13 to be
the total.

1 4 1
2 5 1
3 6 1
4 7 1
5 8 1
6 9 1
7 10 1
8 11 1
12 1
10 13 1
11 14 1
12 15 1
14 16 1
15 17 1
0 18 1
=sum(xxx)
107 144 13


thanks


"Mike H" wrote:

Hmm,

I suspect were into VB now. Let us assume these are the last cells in
columns I & J. What result do you expect and why?

1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 11
12
10 13
11 14
12 15
14 16
15 17
0 18

Mike

"vdubluv74" wrote:

That worked perfect, but I messed up on what i needed. so here is the updated
version

find the last entry in a cell and calculate the previous 13 cells. the
previous cells may be blank. because i have fomulas in the other cells i
would need something like this.

if (the last cell in column I is greater than 0) then (take the last cell in
column J, next to column I, so if the last cell in I is I10 the formula would
use J10. and add the number in the previous 13 cells in that column)

wow

"Mike H" wrote:

Tr this

=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000)

This an array which must be entered using CTRL+Shift+Enter and not just enter.
If you do it correctly then Excel will put curly brackets around the formula
{}. You can't type these yourself..

Mike

"vdubluv74" wrote:

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks

Sum last 13 cells in column
 

That did not come out right on the page. all of the 1s will be in column k
and that formula will be in column k as well


"vdubluv74" wrote:

Hi, I need a formula, working with excel 2003, and very new to it.

so with those being the last cells this is what I would need the answers to
be

107 144

those are the totals from the 3rd row to the bottom. but if you had 1s in
the next column and then a formula after the last cells i would need 13 to be
the total.

1 4 1
2 5 1
3 6 1
4 7 1
5 8 1
6 9 1
7 10 1
8 11 1
12 1
10 13 1
11 14 1
12 15 1
14 16 1
15 17 1
0 18 1
=sum(xxx)
107 144 13


thanks


"Mike H" wrote:

Hmm,

I suspect were into VB now. Let us assume these are the last cells in
columns I & J. What result do you expect and why?

1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 11
12
10 13
11 14
12 15
14 16
15 17
0 18

Mike

"vdubluv74" wrote:

That worked perfect, but I messed up on what i needed. so here is the updated
version

find the last entry in a cell and calculate the previous 13 cells. the
previous cells may be blank. because i have fomulas in the other cells i
would need something like this.

if (the last cell in column I is greater than 0) then (take the last cell in
column J, next to column I, so if the last cell in I is I10 the formula would
use J10. and add the number in the previous 13 cells in that column)

wow

"Mike H" wrote:

Tr this

=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000)

This an array which must be entered using CTRL+Shift+Enter and not just enter.
If you do it correctly then Excel will put curly brackets around the formula
{}. You can't type these yourself..

Mike

"vdubluv74" wrote:

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks

Sum last 13 cells in column
 

Hi,

I can see the logic for the left column(I) i think. because there is a blank
you have added the value from the next column. But I can't see the logic or
how you get 144 for then next. However, try this function.

Alt + F11 to open VB editor.
Right click 'This Workbook' and insert module and paste the code below in on
the right.

You call the code like this

=CountUp(n,n)

the first n is the column Number (9 for column i)
the second n is the amount of numbers you want to count. So

=countup(9,13)
count the last 13 entries and if there are blanks it counts the column to
the right.
I have added no error trapping so if it finds text it falls over.

Function CountUp(col As Integer, num As Integer)
Numcounted = 0
lastrow = Cells(Rows.Count, col).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, col).Value < "" Then
CountUp = CountUp + Cells(x, col).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
Else
CountUp = CountUp + Cells(x, col).Offset(, 1).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
End If

Next
getmeout:
End Function


Mike


"vdubluv74" wrote:

Hi, I need a formula, working with excel 2003, and very new to it.

so with those being the last cells this is what I would need the answers to
be

107 144

those are the totals from the 3rd row to the bottom. but if you had 1s in
the next column and then a formula after the last cells i would need 13 to be
the total.

1 4 1
2 5 1
3 6 1
4 7 1
5 8 1
6 9 1
7 10 1
8 11 1
12 1
10 13 1
11 14 1
12 15 1
14 16 1
15 17 1
0 18 1
=sum(xxx)
107 144 13


thanks


"Mike H" wrote:

Hmm,

I suspect were into VB now. Let us assume these are the last cells in
columns I & J. What result do you expect and why?

1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 11
12
10 13
11 14
12 15
14 16
15 17
0 18

Mike

"vdubluv74" wrote:

That worked perfect, but I messed up on what i needed. so here is the updated
version

find the last entry in a cell and calculate the previous 13 cells. the
previous cells may be blank. because i have fomulas in the other cells i
would need something like this.

if (the last cell in column I is greater than 0) then (take the last cell in
column J, next to column I, so if the last cell in I is I10 the formula would
use J10. and add the number in the previous 13 cells in that column)

wow

"Mike H" wrote:

Tr this

=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000)

This an array which must be entered using CTRL+Shift+Enter and not just enter.
If you do it correctly then Excel will put curly brackets around the formula
{}. You can't type these yourself..

Mike

"vdubluv74" wrote:

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks

Sum last 13 cells in column
 

I think we are getting really close.

here is what I saw in the columns

[ I ][ J ][ K ]
[1 ][ 4 ][ 1 ]
[2 ][ 5 ][ 1]
[3 ][ 6 ][ 1]
[4 ][ 7 ][ 1]
[ 5][ 8 ][ 1 ]
[ 6 ][ 9 ][ 1 ]
[ 7 ][10][ 1 ]
[ 8 ][11][ 1 ]
[12][ ][ 1 ]
[10][13][1]
[11][14][1]
[12][15][1]
[14][16][1]
[15][17][1]
[0][18][1]
[ ][ ][=sum(xxx)]
[107][144][13]

the new formula would not use the space next to it. Also this spreadsheet
gets added to mostly everyday. I use the spreadsheet to track the flights I
do, so one row is one flight and at the end of 13 rows is one page in my log
book and I was trying to make it easier to have the spreadsheet add all of
the numbers from the 13 previous flights


thanks for your help



"Mike H" wrote:

Hi,

I can see the logic for the left column(I) i think. because there is a blank
you have added the value from the next column. But I can't see the logic or
how you get 144 for then next. However, try this function.

Alt + F11 to open VB editor.
Right click 'This Workbook' and insert module and paste the code below in on
the right.

You call the code like this

=CountUp(n,n)

the first n is the column Number (9 for column i)
the second n is the amount of numbers you want to count. So

=countup(9,13)
count the last 13 entries and if there are blanks it counts the column to
the right.
I have added no error trapping so if it finds text it falls over.

Function CountUp(col As Integer, num As Integer)
Numcounted = 0
lastrow = Cells(Rows.Count, col).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, col).Value < "" Then
CountUp = CountUp + Cells(x, col).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
Else
CountUp = CountUp + Cells(x, col).Offset(, 1).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
End If

Next
getmeout:
End Function


Mike


"vdubluv74" wrote:

Hi, I need a formula, working with excel 2003, and very new to it.

so with those being the last cells this is what I would need the answers to
be

107 144

those are the totals from the 3rd row to the bottom. but if you had 1s in
the next column and then a formula after the last cells i would need 13 to be
the total.

1 4 1
2 5 1
3 6 1
4 7 1
5 8 1
6 9 1
7 10 1
8 11 1
12 1
10 13 1
11 14 1
12 15 1
14 16 1
15 17 1
0 18 1
=sum(xxx)
107 144 13


thanks


"Mike H" wrote:

Hmm,

I suspect were into VB now. Let us assume these are the last cells in
columns I & J. What result do you expect and why?

1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 11
12
10 13
11 14
12 15
14 16
15 17
0 18

Mike

"vdubluv74" wrote:

That worked perfect, but I messed up on what i needed. so here is the updated
version

find the last entry in a cell and calculate the previous 13 cells. the
previous cells may be blank. because i have fomulas in the other cells i
would need something like this.

if (the last cell in column I is greater than 0) then (take the last cell in
column J, next to column I, so if the last cell in I is I10 the formula would
use J10. and add the number in the previous 13 cells in that column)

wow

"Mike H" wrote:

Tr this

=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000)

This an array which must be entered using CTRL+Shift+Enter and not just enter.
If you do it correctly then Excel will put curly brackets around the formula
{}. You can't type these yourself..

Mike

"vdubluv74" wrote:

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks

Sum last 13 cells in column
 

I can't understand the logic of how you get 107 & 144 for the 2 columns and
the formatting of the post isn't helping. Post your file here (Its free) with
the cells you use to get 117 and 144 highlighted and post the link and let me
have a look.

http://www.savefile.com/

Mike

"vdubluv74" wrote:

I think we are getting really close.

here is what I saw in the columns

[ I ][ J ][ K ]
[1 ][ 4 ][ 1 ]
[2 ][ 5 ][ 1]
[3 ][ 6 ][ 1]
[4 ][ 7 ][ 1]
[ 5][ 8 ][ 1 ]
[ 6 ][ 9 ][ 1 ]
[ 7 ][10][ 1 ]
[ 8 ][11][ 1 ]
[12][ ][ 1 ]
[10][13][1]
[11][14][1]
[12][15][1]
[14][16][1]
[15][17][1]
[0][18][1]
[ ][ ][=sum(xxx)]
[107][144][13]

the new formula would not use the space next to it. Also this spreadsheet
gets added to mostly everyday. I use the spreadsheet to track the flights I
do, so one row is one flight and at the end of 13 rows is one page in my log
book and I was trying to make it easier to have the spreadsheet add all of
the numbers from the 13 previous flights


thanks for your help



"Mike H" wrote:

Hi,

I can see the logic for the left column(I) i think. because there is a blank
you have added the value from the next column. But I can't see the logic or
how you get 144 for then next. However, try this function.

Alt + F11 to open VB editor.
Right click 'This Workbook' and insert module and paste the code below in on
the right.

You call the code like this

=CountUp(n,n)

the first n is the column Number (9 for column i)
the second n is the amount of numbers you want to count. So

=countup(9,13)
count the last 13 entries and if there are blanks it counts the column to
the right.
I have added no error trapping so if it finds text it falls over.

Function CountUp(col As Integer, num As Integer)
Numcounted = 0
lastrow = Cells(Rows.Count, col).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, col).Value < "" Then
CountUp = CountUp + Cells(x, col).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
Else
CountUp = CountUp + Cells(x, col).Offset(, 1).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
End If

Next
getmeout:
End Function


Mike


"vdubluv74" wrote:

Hi, I need a formula, working with excel 2003, and very new to it.

so with those being the last cells this is what I would need the answers to
be

107 144

those are the totals from the 3rd row to the bottom. but if you had 1s in
the next column and then a formula after the last cells i would need 13 to be
the total.

1 4 1
2 5 1
3 6 1
4 7 1
5 8 1
6 9 1
7 10 1
8 11 1
12 1
10 13 1
11 14 1
12 15 1
14 16 1
15 17 1
0 18 1
=sum(xxx)
107 144 13


thanks


"Mike H" wrote:

Hmm,

I suspect were into VB now. Let us assume these are the last cells in
columns I & J. What result do you expect and why?

1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 11
12
10 13
11 14
12 15
14 16
15 17
0 18

Mike

"vdubluv74" wrote:

That worked perfect, but I messed up on what i needed. so here is the updated
version

find the last entry in a cell and calculate the previous 13 cells. the
previous cells may be blank. because i have fomulas in the other cells i
would need something like this.

if (the last cell in column I is greater than 0) then (take the last cell in
column J, next to column I, so if the last cell in I is I10 the formula would
use J10. and add the number in the previous 13 cells in that column)

wow

"Mike H" wrote:

Tr this

=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000)

This an array which must be entered using CTRL+Shift+Enter and not just enter.
If you do it correctly then Excel will put curly brackets around the formula
{}. You can't type these yourself..

Mike

"vdubluv74" wrote:

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks

Sum last 13 cells in column
 

hi, just uploaded it, my math was a little off. no wonder why!

http://www.savefile.com/files/1808407

thanks

"Mike H" wrote:

I can't understand the logic of how you get 107 & 144 for the 2 columns and
the formatting of the post isn't helping. Post your file here (Its free) with
the cells you use to get 117 and 144 highlighted and post the link and let me
have a look.

http://www.savefile.com/

Mike

"vdubluv74" wrote:

I think we are getting really close.

here is what I saw in the columns

[ I ][ J ][ K ]
[1 ][ 4 ][ 1 ]
[2 ][ 5 ][ 1]
[3 ][ 6 ][ 1]
[4 ][ 7 ][ 1]
[ 5][ 8 ][ 1 ]
[ 6 ][ 9 ][ 1 ]
[ 7 ][10][ 1 ]
[ 8 ][11][ 1 ]
[12][ ][ 1 ]
[10][13][1]
[11][14][1]
[12][15][1]
[14][16][1]
[15][17][1]
[0][18][1]
[ ][ ][=sum(xxx)]
[107][144][13]

the new formula would not use the space next to it. Also this spreadsheet
gets added to mostly everyday. I use the spreadsheet to track the flights I
do, so one row is one flight and at the end of 13 rows is one page in my log
book and I was trying to make it easier to have the spreadsheet add all of
the numbers from the 13 previous flights


thanks for your help



"Mike H" wrote:

Hi,

I can see the logic for the left column(I) i think. because there is a blank
you have added the value from the next column. But I can't see the logic or
how you get 144 for then next. However, try this function.

Alt + F11 to open VB editor.
Right click 'This Workbook' and insert module and paste the code below in on
the right.

You call the code like this

=CountUp(n,n)

the first n is the column Number (9 for column i)
the second n is the amount of numbers you want to count. So

=countup(9,13)
count the last 13 entries and if there are blanks it counts the column to
the right.
I have added no error trapping so if it finds text it falls over.

Function CountUp(col As Integer, num As Integer)
Numcounted = 0
lastrow = Cells(Rows.Count, col).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, col).Value < "" Then
CountUp = CountUp + Cells(x, col).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
Else
CountUp = CountUp + Cells(x, col).Offset(, 1).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
End If

Next
getmeout:
End Function


Mike


"vdubluv74" wrote:

Hi, I need a formula, working with excel 2003, and very new to it.

so with those being the last cells this is what I would need the answers to
be

107 144

those are the totals from the 3rd row to the bottom. but if you had 1s in
the next column and then a formula after the last cells i would need 13 to be
the total.

1 4 1
2 5 1
3 6 1
4 7 1
5 8 1
6 9 1
7 10 1
8 11 1
12 1
10 13 1
11 14 1
12 15 1
14 16 1
15 17 1
0 18 1
=sum(xxx)
107 144 13


thanks


"Mike H" wrote:

Hmm,

I suspect were into VB now. Let us assume these are the last cells in
columns I & J. What result do you expect and why?

1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 11
12
10 13
11 14
12 15
14 16
15 17
0 18

Mike

"vdubluv74" wrote:

That worked perfect, but I messed up on what i needed. so here is the updated
version

find the last entry in a cell and calculate the previous 13 cells. the
previous cells may be blank. because i have fomulas in the other cells i
would need something like this.

if (the last cell in column I is greater than 0) then (take the last cell in
column J, next to column I, so if the last cell in I is I10 the formula would
use J10. and add the number in the previous 13 cells in that column)

wow

"Mike H" wrote:

Tr this

=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000)

This an array which must be entered using CTRL+Shift+Enter and not just enter.
If you do it correctly then Excel will put curly brackets around the formula
{}. You can't type these yourself..

Mike

"vdubluv74" wrote:

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks

Sum last 13 cells in column
 

Hi, just uploaded a better one to explain more.

http://www.savefile.com/files/1808407

So if I add a new flight date and input new take offs and flight hours it
will update the latest 13 entries.

Sum last 13 cells in column
 

what about doing something like this

if I created a cell to enter what row the last flight was on could a formula
calculate the previous 13 cells in that row?

so the last flight was on row 474, i enter 474 in cell b1100 and then the
formula in cell I1100 adds the cells from I474 to I461


thanks


"vdubluv74" wrote:

Hi, just uploaded a better one to explain more.

http://www.savefile.com/files/1808407

So if I add a new flight date and input new take offs and flight hours it
will update the latest 13 entries.

Sum last 13 cells in column
 

A couple of formula

http://www.savefile.com/files/1808452

Mike

"vdubluv74" wrote:

hi, just uploaded it, my math was a little off. no wonder why!

http://www.savefile.com/files/1808407

thanks

"Mike H" wrote:

I can't understand the logic of how you get 107 & 144 for the 2 columns and
the formatting of the post isn't helping. Post your file here (Its free) with
the cells you use to get 117 and 144 highlighted and post the link and let me
have a look.

http://www.savefile.com/

Mike

"vdubluv74" wrote:

I think we are getting really close.

here is what I saw in the columns

[ I ][ J ][ K ]
[1 ][ 4 ][ 1 ]
[2 ][ 5 ][ 1]
[3 ][ 6 ][ 1]
[4 ][ 7 ][ 1]
[ 5][ 8 ][ 1 ]
[ 6 ][ 9 ][ 1 ]
[ 7 ][10][ 1 ]
[ 8 ][11][ 1 ]
[12][ ][ 1 ]
[10][13][1]
[11][14][1]
[12][15][1]
[14][16][1]
[15][17][1]
[0][18][1]
[ ][ ][=sum(xxx)]
[107][144][13]

the new formula would not use the space next to it. Also this spreadsheet
gets added to mostly everyday. I use the spreadsheet to track the flights I
do, so one row is one flight and at the end of 13 rows is one page in my log
book and I was trying to make it easier to have the spreadsheet add all of
the numbers from the 13 previous flights


thanks for your help



"Mike H" wrote:

Hi,

I can see the logic for the left column(I) i think. because there is a blank
you have added the value from the next column. But I can't see the logic or
how you get 144 for then next. However, try this function.

Alt + F11 to open VB editor.
Right click 'This Workbook' and insert module and paste the code below in on
the right.

You call the code like this

=CountUp(n,n)

the first n is the column Number (9 for column i)
the second n is the amount of numbers you want to count. So

=countup(9,13)
count the last 13 entries and if there are blanks it counts the column to
the right.
I have added no error trapping so if it finds text it falls over.

Function CountUp(col As Integer, num As Integer)
Numcounted = 0
lastrow = Cells(Rows.Count, col).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, col).Value < "" Then
CountUp = CountUp + Cells(x, col).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
Else
CountUp = CountUp + Cells(x, col).Offset(, 1).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
End If

Next
getmeout:
End Function


Mike


"vdubluv74" wrote:

Hi, I need a formula, working with excel 2003, and very new to it.

so with those being the last cells this is what I would need the answers to
be

107 144

those are the totals from the 3rd row to the bottom. but if you had 1s in
the next column and then a formula after the last cells i would need 13 to be
the total.

1 4 1
2 5 1
3 6 1
4 7 1
5 8 1
6 9 1
7 10 1
8 11 1
12 1
10 13 1
11 14 1
12 15 1
14 16 1
15 17 1
0 18 1
=sum(xxx)
107 144 13


thanks


"Mike H" wrote:

Hmm,

I suspect were into VB now. Let us assume these are the last cells in
columns I & J. What result do you expect and why?

1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 11
12
10 13
11 14
12 15
14 16
15 17
0 18

Mike

"vdubluv74" wrote:

That worked perfect, but I messed up on what i needed. so here is the updated
version

find the last entry in a cell and calculate the previous 13 cells. the
previous cells may be blank. because i have fomulas in the other cells i
would need something like this.

if (the last cell in column I is greater than 0) then (take the last cell in
column J, next to column I, so if the last cell in I is I10 the formula would
use J10. and add the number in the previous 13 cells in that column)

wow

"Mike H" wrote:

Tr this

=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000)

This an array which must be entered using CTRL+Shift+Enter and not just enter.
If you do it correctly then Excel will put curly brackets around the formula
{}. You can't type these yourself..

Mike

"vdubluv74" wrote:

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks

Sum last 13 cells in column
 

great, thanks for your help

"Mike H" wrote:

A couple of formula

http://www.savefile.com/files/1808452

Mike

"vdubluv74" wrote:

hi, just uploaded it, my math was a little off. no wonder why!

http://www.savefile.com/files/1808407

thanks

"Mike H" wrote:

I can't understand the logic of how you get 107 & 144 for the 2 columns and
the formatting of the post isn't helping. Post your file here (Its free) with
the cells you use to get 117 and 144 highlighted and post the link and let me
have a look.

http://www.savefile.com/

Mike

"vdubluv74" wrote:

I think we are getting really close.

here is what I saw in the columns

[ I ][ J ][ K ]
[1 ][ 4 ][ 1 ]
[2 ][ 5 ][ 1]
[3 ][ 6 ][ 1]
[4 ][ 7 ][ 1]
[ 5][ 8 ][ 1 ]
[ 6 ][ 9 ][ 1 ]
[ 7 ][10][ 1 ]
[ 8 ][11][ 1 ]
[12][ ][ 1 ]
[10][13][1]
[11][14][1]
[12][15][1]
[14][16][1]
[15][17][1]
[0][18][1]
[ ][ ][=sum(xxx)]
[107][144][13]

the new formula would not use the space next to it. Also this spreadsheet
gets added to mostly everyday. I use the spreadsheet to track the flights I
do, so one row is one flight and at the end of 13 rows is one page in my log
book and I was trying to make it easier to have the spreadsheet add all of
the numbers from the 13 previous flights


thanks for your help



"Mike H" wrote:

Hi,

I can see the logic for the left column(I) i think. because there is a blank
you have added the value from the next column. But I can't see the logic or
how you get 144 for then next. However, try this function.

Alt + F11 to open VB editor.
Right click 'This Workbook' and insert module and paste the code below in on
the right.

You call the code like this

=CountUp(n,n)

the first n is the column Number (9 for column i)
the second n is the amount of numbers you want to count. So

=countup(9,13)
count the last 13 entries and if there are blanks it counts the column to
the right.
I have added no error trapping so if it finds text it falls over.

Function CountUp(col As Integer, num As Integer)
Numcounted = 0
lastrow = Cells(Rows.Count, col).End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, col).Value < "" Then
CountUp = CountUp + Cells(x, col).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
Else
CountUp = CountUp + Cells(x, col).Offset(, 1).Value
Numcounted = Numcounted + 1
If Numcounted = num Then GoTo getmeout
End If

Next
getmeout:
End Function


Mike


"vdubluv74" wrote:

Hi, I need a formula, working with excel 2003, and very new to it.

so with those being the last cells this is what I would need the answers to
be

107 144

those are the totals from the 3rd row to the bottom. but if you had 1s in
the next column and then a formula after the last cells i would need 13 to be
the total.

1 4 1
2 5 1
3 6 1
4 7 1
5 8 1
6 9 1
7 10 1
8 11 1
12 1
10 13 1
11 14 1
12 15 1
14 16 1
15 17 1
0 18 1
=sum(xxx)
107 144 13


thanks


"Mike H" wrote:

Hmm,

I suspect were into VB now. Let us assume these are the last cells in
columns I & J. What result do you expect and why?

1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 11
12
10 13
11 14
12 15
14 16
15 17
0 18

Mike

"vdubluv74" wrote:

That worked perfect, but I messed up on what i needed. so here is the updated
version

find the last entry in a cell and calculate the previous 13 cells. the
previous cells may be blank. because i have fomulas in the other cells i
would need something like this.

if (the last cell in column I is greater than 0) then (take the last cell in
column J, next to column I, so if the last cell in I is I10 the formula would
use J10. and add the number in the previous 13 cells in that column)

wow

"Mike H" wrote:

Tr this

=SUM(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1000< 0),13)):A1000)

This an array which must be entered using CTRL+Shift+Enter and not just enter.
If you do it correctly then Excel will put curly brackets around the formula
{}. You can't type these yourself..

Mike

"vdubluv74" wrote:

I am looking for a formula that calculates the last 13 numbers in a column
that are greater than 0. thanks