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