Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add every other column (Excel 2003)

    i want to create a formula that will add every other column
    e.g
    In column B will be the price of an item,
    Coumn C will be the number sold in week 1,
    Column D is the price * number sold in week 1
    column E is number sold in week 2
    column F is the price * number sold in week 2..... etc etc...

    So over time I want to add Coumn D, F,H, J etc, already this spread
    sheet has grown to column CI to include each week in the year so I don't
    want to have to type in the formula or click on each cell

    It must be simple

    Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add every other column (Excel 2003)

    If you put the formula in the uppermost cell, you can easily fill down, for example by double clicking the fill handle in the lower right corner of the cell.

    You can also use a macro, like this:

    Sub FillFormulas()
    Const lngFirstRow = 2 ' assuming first row contains headers
    Const lngLastRow = 200 ' change to whatever is appropriate
    Const lngFirstCol = 4 ' first column is D
    Const lngNumCols = 52 ' weeks in the year
    Const lngStep = 2 ' space between formula columns
    Dim lngRow As Long
    Dim lngCol As Long
    For lngCol = lngFirstCol To lngFirstCol + lngStep * lngNumCols Step lngStep
    For lngRow = lngFirstRow To lngLastRow
    Cells(lngRow, lngCol).FormulaR1C1 = "=RC2*RC[-1]"
    Next lngRow
    Next lngCol
    End Sub

    Change the contants at the beginning to whatever you need.
    Save your workbook before trying it, so that you can close without saving if things go wrong.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add every other column (Excel 2003)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    To do this in a formula (borrowing from Chip Pearson's web site), you can do this by entering an array formula such as

    =SUM((C2:L2="Amt")*C3:L3) You have to enter this as an array, so after typing the formula you would press Ctl Shft and Enter.

    this formula would assume that the heading to every column that has the price*number sold formula in it has a label of "Amt". For an explanation of what the formula is doing, check http://www.cpearson.com/excel/array.htm

    Does that give you any better ideas as opposed to typing in "=b2+d2+f2+h2..."?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Add every other column (Excel 2003)

    Hi,
    If you define a name to give you the data range for each row, you can then use an array formula to add every other column. E.g. if your data starts in row1, select a cell in row1 then define a range called datarow as:
    =offset(C1,0,0,1,counta(C1:IV1))
    This should give you the used range in whatever row you are in from column C to IV (the last possible column)
    Then in column A you can have your total formula for the row being:
    =sum((datarow)*(MOD(COLUMN(datarow),2)=0))
    array-entered.
    Hope that helps.
    Note: this assumes there will be no gaps in a row of data - if there may be, you will have to revise slightly.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add every other column (Excel 2003)

    If the range is D1:CI1, then you can use an array formula like this:

    <pre>=SUM((MOD(COLUMN(D1:CI1),2)=0)*D1:CI1)
    </pre>


    Again, that is an array formula so hold down Ctrl+Shift when you press Enter to enter the formula into the cell.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add every other column (Excel 2003)

    That's very clever...it took me about 5 minutes to figure out what the formula was doing. So it looks like it works if the cells you want to total are on the even rows, as was the scenario given in the original question. To get it to sum the odd rows, you would want to change the =0 in the formula to <>0. Then the formula returns a 1 for true or 0 for false depending on if the cell is in an even column or an odd column. Then the 1 is multiplied by the sales amount (price x quantity) and the 0 is multiplied by the quantity sold and those results are summed!

    That could come in handy...

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add every other column (Excel 2003)

    That is correct with one small correction to what you said. You said:

    <hr>
    So it looks like it works if the cells you want to total are on the even rows
    <hr>

    That should read: So it looks like it works if the cells you want to total are on the even columns.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add every other column (Excel 2003)

    Good catch...I meant to say columns.

  9. #9
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add every other column (Excel 2003)

    While others have already addressed your original question, I'll be the first to say that you're probably setting up your data inefficiently. Excel only allows 256 columns so you'll only have room for about 2.5 years of data.

    It would probably be better to set up your data as a database as follows:
    Column A would be the week number (or the starting date of the week, whichever suits you best)
    Column B would be item description
    Column C would be unit price
    Column D would be number of units sold
    Column E would be revenue (Column C times Column D)

    You could repeat columns B-E going across the sheet for each item. The 256 column limit will restrict you to 63 items per week. If you have more items than that each week, you could have a new row for each item each week. Excel allows for 65536 rows. If you have 100 items each week, you'll be able to go for 655 weeks.

    Setting up your data in this fashion lets you make use of SUMIF, DSUM and other similar functions. This also lets you change the price for each item every week.

Posting Permissions

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