Skipping Columns
 

There must be a simple solution to this, but I'm having trouble finding
it:

I have two spreadsheets. One has daily sales totals that is updated
every day. The other references those totals, but adds them together
in a cumulative sum.

Sheet 1:
6/1 6/2 6/3
5 $10 6 $12 4 $8

On Sheet 2, I'd like to show cumulative totals:
6/1 6/2 6/3
$10 $22 $30

Problem is, on Sheet 1 I have an extra column in between each daily
total (quantity, as you can see in my "example")

Right now to get the cumulative total for a day I can reference the
previous day's cumulative total (in the immediately preceding cell on
the current sheet) and adding to it that day's daily total from the
other sheet. I can do this manually, but I don't have time to adjust it
for an entire month, times twelve different items.

Is there a way to adjust my formula so that I can just copy & paste it
across my sheet? What I need is a way to skip a column in my formula...

Skipping Columns
 

Here is one solution. For this example, assume your Sheet1 data is on row 6,
starting in column A. If I understand your description correctly, column A
has a quantity, column B has sales $, etc. Therefore, you want a cumulative
total of only the even-numbered columns (B, D, F, etc.)

1. In A7 on Sheet1, enter this formula:
=IF(MOD(COLUMN(),2)=1,0,A6)
and copy it across all your columns of data. You can hide this row if you
want.

2. In column A on Sheet2 (6/1 in your example), enter this formula:
=SUM(Sheet1!$A7:OFFSET(Sheet1!A7,0,(COLUMN()*2)-COLUMN()))
and copy it across.

This will give you a cumulative total of only the sales $ columns. If my
example is mistaken, and you need to total the odd-numbered columns (A, C, E,
etc.) , change =1 in the Sheet1 formula to =0.

Hope this helps,

Hutch

" wrote:

There must be a simple solution to this, but I'm having trouble finding
it:

I have two spreadsheets. One has daily sales totals that is updated
every day. The other references those totals, but adds them together
in a cumulative sum.

Sheet 1:
6/1 6/2 6/3
5 $10 6 $12 4 $8

On Sheet 2, I'd like to show cumulative totals:
6/1 6/2 6/3
$10 $22 $30

Problem is, on Sheet 1 I have an extra column in between each daily
total (quantity, as you can see in my "example")

Right now to get the cumulative total for a day I can reference the
previous day's cumulative total (in the immediately preceding cell on
the current sheet) and adding to it that day's daily total from the
other sheet. I can do this manually, but I don't have time to adjust it
for an entire month, times twelve different items.

Is there a way to adjust my formula so that I can just copy & paste it
across my sheet? What I need is a way to skip a column in my formula...

Skipping Columns
 

Here is a different approach. Same assumptions as my previous post, but with
this method, no extra formula is needed on Sheet1. Enter the following array
formula in column A (6/1 in your example) on Sheet2, and copy across:

=SUM(IF(MOD(COLUMN(Sheet1!$A6:OFFSET(Sheet1!$A6,0, COLUMN()*2)),2)=0,Sheet1!$A6:OFFSET(Sheet1!$A6,0,C OLUMN()*2)))

Please note that this is an array formula, so instead of {Enter} you have to
press {Ctrl}{Shift}{Enter} to enter it. If you do it correctly, the formula
will show curly braces around it (you can't enter the curly braces yourself).
You can copy & paste or drag it to other cells normally.

Gives the same result as the other approach.

Hope this helps,

Hutch

" wrote:

There must be a simple solution to this, but I'm having trouble finding
it:

I have two spreadsheets. One has daily sales totals that is updated
every day. The other references those totals, but adds them together
in a cumulative sum.

Sheet 1:
6/1 6/2 6/3
5 $10 6 $12 4 $8

On Sheet 2, I'd like to show cumulative totals:
6/1 6/2 6/3
$10 $22 $30

Problem is, on Sheet 1 I have an extra column in between each daily
total (quantity, as you can see in my "example")

Right now to get the cumulative total for a day I can reference the
previous day's cumulative total (in the immediately preceding cell on
the current sheet) and adding to it that day's daily total from the
other sheet. I can do this manually, but I don't have time to adjust it
for an entire month, times twelve different items.

Is there a way to adjust my formula so that I can just copy & paste it
across my sheet? What I need is a way to skip a column in my formula...