+ Reply to Thread
Results 1 to 3 of 3

Skipping Columns

  1. #1
    enricospada@gmail.com
    Guest

    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...


  2. #2
    Tom Hutchins
    Guest

    RE: 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

    "enricospada@gmail.com" 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...
    >
    >


  3. #3
    Tom Hutchins
    Guest

    RE: 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,COLUMN()*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

    "enricospada@gmail.com" 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...
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1