Results 1 to 9 of 9

20040818, 15:19 #1
 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?

20040818, 15:32 #2
 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.

20040818, 16:06 #3
 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..."?

20040818, 16:20 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,284
 Thanks
 3
 Thanked 192 Times in 178 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))
arrayentered.
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

20040818, 17:52 #5
 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

20040818, 19:09 #6
 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...

20040818, 20:26 #7
 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

20040818, 21:58 #8
 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.

20040819, 04:29 #9
 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 BE 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.