I have an Excel spreadsheet with numbers that I want to sum in a row, carry a
cummulative sum in a column, and be able to skip a few rows and still carry
the cummulative number by picking up the same formula. However, once I skip a
few rows with no information, Excel wants to only add the row and not carry
forward the previous sum. Clear as mud?? Can you help?
--
Bae

Maybe something like this:

With
Numeric values in the columns B:K, beginning in Row_2
and some rows skipped

A2: =SUM(B2:K2)
A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0))

The formula in A3 can be copied anywhere down Col_A, skipping rows if needed.
It will continue to calculate the cumulative total.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

I have an Excel spreadsheet with numbers that I want to sum in a row, carry a
cummulative sum in a column, and be able to skip a few rows and still carry
the cummulative number by picking up the same formula. However, once I skip a
few rows with no information, Excel wants to only add the row and not carry
forward the previous sum. Clear as mud?? Can you help?
--
Bae

Ron,
I am not sure if I am just not getting it or what, but that did not work. If
I copy the formula from A3, it is the calculation from the previous formula.
I am somwhat of a novice working with formulas, so maybe I am just not
getting it...
--
Bae


"Ron Coderre" wrote:

Maybe something like this:

With
Numeric values in the columns B:K, beginning in Row_2
and some rows skipped

A2: =SUM(B2:K2)
A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0))

The formula in A3 can be copied anywhere down Col_A, skipping rows if needed.
It will continue to calculate the cumulative total.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

I have an Excel spreadsheet with numbers that I want to sum in a row, carry a
cummulative sum in a column, and be able to skip a few rows and still carry
the cummulative number by picking up the same formula. However, once I skip a
few rows with no information, Excel wants to only add the row and not carry
forward the previous sum. Clear as mud?? Can you help?
--
Bae

Let's work on the data structure first, to make sure we're talking about the
same thing.

Here's my interpretation of what you want:

With
Refs Col_A Col_B Col_C Col_D
Row_1 (blank) (blank) (blank) (blank)
Row_2 6 1 2 3
Row_3 36 10 10 10
Row_4 54 5 6 7
Row_5 (blank) (blank) (blank) (blank)
Row_6 57 1 1 1
Row_7 (blank) (blank) (blank) (blank)
Row_8 75 5 6 7

Where
Col_A contains the summary formulas.
A2 simply sums from B2:D2
(1+2+3=6)

A3 sums B3:D3 and adds it to the cumulative total of from above
(10+10+10=30 + 6 from cell A2 =36)

A4 sums B4:D4 and adds it to the cumulative total of from above
(5+6+7=18 + 36 from cell A3 =54)
etc

Am I on the right track here?

***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

Ron,
I am not sure if I am just not getting it or what, but that did not work. If
I copy the formula from A3, it is the calculation from the previous formula.
I am somwhat of a novice working with formulas, so maybe I am just not
getting it...
--
Bae


"Ron Coderre" wrote:

Maybe something like this:

With
Numeric values in the columns B:K, beginning in Row_2
and some rows skipped

A2: =SUM(B2:K2)
A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0))

The formula in A3 can be copied anywhere down Col_A, skipping rows if needed.
It will continue to calculate the cumulative total.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

I have an Excel spreadsheet with numbers that I want to sum in a row, carry a
cummulative sum in a column, and be able to skip a few rows and still carry
the cummulative number by picking up the same formula. However, once I skip a
few rows with no information, Excel wants to only add the row and not carry
forward the previous sum. Clear as mud?? Can you help?
--
Bae

You are on the right track! Now what?
--
Bae


"Ron Coderre" wrote:

Let's work on the data structure first, to make sure we're talking about the
same thing.

Here's my interpretation of what you want:

With
Refs Col_A Col_B Col_C Col_D
Row_1 (blank) (blank) (blank) (blank)
Row_2 6 1 2 3
Row_3 36 10 10 10
Row_4 54 5 6 7
Row_5 (blank) (blank) (blank) (blank)
Row_6 57 1 1 1
Row_7 (blank) (blank) (blank) (blank)
Row_8 75 5 6 7

Where
Col_A contains the summary formulas.
A2 simply sums from B2:D2
(1+2+3=6)

A3 sums B3:D3 and adds it to the cumulative total of from above
(10+10+10=30 + 6 from cell A2 =36)

A4 sums B4:D4 and adds it to the cumulative total of from above
(5+6+7=18 + 36 from cell A3 =54)
etc

Am I on the right track here?

***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

Ron,
I am not sure if I am just not getting it or what, but that did not work. If
I copy the formula from A3, it is the calculation from the previous formula.
I am somwhat of a novice working with formulas, so maybe I am just not
getting it...
--
Bae


"Ron Coderre" wrote:

Maybe something like this:

With
Numeric values in the columns B:K, beginning in Row_2
and some rows skipped

A2: =SUM(B2:K2)
A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0))

The formula in A3 can be copied anywhere down Col_A, skipping rows if needed.
It will continue to calculate the cumulative total.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

I have an Excel spreadsheet with numbers that I want to sum in a row, carry a
cummulative sum in a column, and be able to skip a few rows and still carry
the cummulative number by picking up the same formula. However, once I skip a
few rows with no information, Excel wants to only add the row and not carry
forward the previous sum. Clear as mud?? Can you help?
--
Bae

Before this gets unnecessarily complicated,
let's see if something as simple as this will work for you....

Using my posted table...
A2: =SUM(B$2:D2)
Copy that formula down as far as you need.

How'd we do?
***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

You are on the right track! Now what?
--
Bae


"Ron Coderre" wrote:

Let's work on the data structure first, to make sure we're talking about the
same thing.

Here's my interpretation of what you want:

With
Refs Col_A Col_B Col_C Col_D
Row_1 (blank) (blank) (blank) (blank)
Row_2 6 1 2 3
Row_3 36 10 10 10
Row_4 54 5 6 7
Row_5 (blank) (blank) (blank) (blank)
Row_6 57 1 1 1
Row_7 (blank) (blank) (blank) (blank)
Row_8 75 5 6 7

Where
Col_A contains the summary formulas.
A2 simply sums from B2:D2
(1+2+3=6)

A3 sums B3:D3 and adds it to the cumulative total of from above
(10+10+10=30 + 6 from cell A2 =36)

A4 sums B4:D4 and adds it to the cumulative total of from above
(5+6+7=18 + 36 from cell A3 =54)
etc

Am I on the right track here?

***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

Ron,
I am not sure if I am just not getting it or what, but that did not work. If
I copy the formula from A3, it is the calculation from the previous formula.
I am somwhat of a novice working with formulas, so maybe I am just not
getting it...
--
Bae


"Ron Coderre" wrote:

Maybe something like this:

With
Numeric values in the columns B:K, beginning in Row_2
and some rows skipped

A2: =SUM(B2:K2)
A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0))

The formula in A3 can be copied anywhere down Col_A, skipping rows if needed.
It will continue to calculate the cumulative total.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

I have an Excel spreadsheet with numbers that I want to sum in a row, carry a
cummulative sum in a column, and be able to skip a few rows and still carry
the cummulative number by picking up the same formula. However, once I skip a
few rows with no information, Excel wants to only add the row and not carry
forward the previous sum. Clear as mud?? Can you help?
--
Bae

ToolsOptionsCalculation.

Is "Automatic" checkmarked as it should be?


Gord Dibben MS Excel MVP

On Wed, 1 Nov 2006 13:24:01 -0800, Bonitae
wrote:

Ron,
I am not sure if I am just not getting it or what, but that did not work. If
I copy the formula from A3, it is the calculation from the previous formula.
I am somwhat of a novice working with formulas, so maybe I am just not
getting it...

That does it, thanks!
--
Bae


"Ron Coderre" wrote:

Before this gets unnecessarily complicated,
let's see if something as simple as this will work for you....

Using my posted table...
A2: =SUM(B$2:D2)
Copy that formula down as far as you need.

How'd we do?
***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

You are on the right track! Now what?
--
Bae


"Ron Coderre" wrote:

Let's work on the data structure first, to make sure we're talking about the
same thing.

Here's my interpretation of what you want:

With
Refs Col_A Col_B Col_C Col_D
Row_1 (blank) (blank) (blank) (blank)
Row_2 6 1 2 3
Row_3 36 10 10 10
Row_4 54 5 6 7
Row_5 (blank) (blank) (blank) (blank)
Row_6 57 1 1 1
Row_7 (blank) (blank) (blank) (blank)
Row_8 75 5 6 7

Where
Col_A contains the summary formulas.
A2 simply sums from B2:D2
(1+2+3=6)

A3 sums B3:D3 and adds it to the cumulative total of from above
(10+10+10=30 + 6 from cell A2 =36)

A4 sums B4:D4 and adds it to the cumulative total of from above
(5+6+7=18 + 36 from cell A3 =54)
etc

Am I on the right track here?

***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

Ron,
I am not sure if I am just not getting it or what, but that did not work. If
I copy the formula from A3, it is the calculation from the previous formula.
I am somwhat of a novice working with formulas, so maybe I am just not
getting it...
--
Bae


"Ron Coderre" wrote:

Maybe something like this:

With
Numeric values in the columns B:K, beginning in Row_2
and some rows skipped

A2: =SUM(B2:K2)
A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0))

The formula in A3 can be copied anywhere down Col_A, skipping rows if needed.
It will continue to calculate the cumulative total.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

I have an Excel spreadsheet with numbers that I want to sum in a row, carry a
cummulative sum in a column, and be able to skip a few rows and still carry
the cummulative number by picking up the same formula. However, once I skip a
few rows with no information, Excel wants to only add the row and not carry
forward the previous sum. Clear as mud?? Can you help?
--
Bae

Yes. it is thank you...
--
Bae


"Gord Dibben" wrote:

ToolsOptionsCalculation.

Is "Automatic" checkmarked as it should be?


Gord Dibben MS Excel MVP

On Wed, 1 Nov 2006 13:24:01 -0800, Bonitae
wrote:

Ron,
I am not sure if I am just not getting it or what, but that did not work. If
I copy the formula from A3, it is the calculation from the previous formula.
I am somwhat of a novice working with formulas, so maybe I am just not
getting it...

Be careful what you ask for, because your restrictions without
explanation of why you have to skip some rows can make processing
take a lot longer as your data increases
http://groups.google.com/group/micro...TK2MSF TNGP10

Take a look at
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
if you have to skip some rows for a reason in some fixed formatting
you could change the references arount the empty rows, but it
would be far better to avoid the empty rows.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Bonitae" wrote in message ...
That does it, thanks!
--
Bae


"Ron Coderre" wrote:

Before this gets unnecessarily complicated,
let's see if something as simple as this will work for you....

Using my posted table...
A2: =SUM(B$2:D2)
Copy that formula down as far as you need.

How'd we do?
***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

You are on the right track! Now what?
--
Bae


"Ron Coderre" wrote:

Let's work on the data structure first, to make sure we're talking about the
same thing.

Here's my interpretation of what you want:

With
Refs Col_A Col_B Col_C Col_D
Row_1 (blank) (blank) (blank) (blank)
Row_2 6 1 2 3
Row_3 36 10 10 10
Row_4 54 5 6 7
Row_5 (blank) (blank) (blank) (blank)
Row_6 57 1 1 1
Row_7 (blank) (blank) (blank) (blank)
Row_8 75 5 6 7

Where
Col_A contains the summary formulas.
A2 simply sums from B2:D2
(1+2+3=6)

A3 sums B3:D3 and adds it to the cumulative total of from above
(10+10+10=30 + 6 from cell A2 =36)

A4 sums B4:D4 and adds it to the cumulative total of from above
(5+6+7=18 + 36 from cell A3 =54)
etc

Am I on the right track here?

***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

Ron,
I am not sure if I am just not getting it or what, but that did not work. If
I copy the formula from A3, it is the calculation from the previous formula.
I am somwhat of a novice working with formulas, so maybe I am just not
getting it...
--
Bae


"Ron Coderre" wrote:

Maybe something like this:

With
Numeric values in the columns B:K, beginning in Row_2
and some rows skipped

A2: =SUM(B2:K2)
A3: =SUM(B3:K3)+LOOKUP(10^99,$A$1:OFFSET(A3,-1,0))

The formula in A3 can be copied anywhere down Col_A, skipping rows if needed.
It will continue to calculate the cumulative total.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bonitae" wrote:

I have an Excel spreadsheet with numbers that I want to sum in a row, carry a
cummulative sum in a column, and be able to skip a few rows and still carry
the cummulative number by picking up the same formula. However, once I skip a
few rows with no information, Excel wants to only add the row and not carry
forward the previous sum. Clear as mud?? Can you help?
--
Bae