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

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

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

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

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

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

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