Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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.
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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.

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
compare cells in column to criteria, then average next column cell Bradwin Excel Worksheet Functions 2 July 21st 08 08:37 PM
Display cells(text) in one column based on cells which are present inother column [email protected] Excel Discussion (Misc queries) 1 May 12th 08 01:40 PM
Count number of cells and total in one column, based on another column suffix Pierre Excel Worksheet Functions 5 October 31st 07 01:28 AM
how do I update a column and create new rows for new column cells Pete Excel Discussion (Misc queries) 1 June 6th 07 02:02 AM
Copying a column of single cells into a column of merged cells [email protected] Excel Discussion (Misc queries) 1 August 16th 06 01:18 PM


All times are GMT +1. The time now is 08:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"