Summing Alternate Columns

[imported_unknown]

I need to sum alternatate columns in a large spreadsheet. e.g. Qty, Value, Qty, Value etc and I need to add all the values (which may be in columns headed Jan Feb Mar or other differing headings.

Is there a cunning trick?

Viv

 

[Yogi Anand]

On 2002-10-04 10:53, vhs wrote:
I need to sum alternatate columns in a large spreadsheet. e.g. Qty, Value, Qty, Value etc and I need to add all the values (which may be in columns headed Jan Feb Mar or other differing headings.

Is there a cunning trick?

Viv

Hi Viv:

If I have understood you correctly, you can use the sum function with the arguments appropriately delineated as alternate columns. See the worksheet simulation ...

y021004.xls
	A	B	C	D	E	F	G	H
1	Qty	JanValue	Qty	FebValue	Qty	MarValue		SumofValueColumns
2	1	1.1	1	2.1	1	3.1
3	2	1.2	2	2.2	2	3.2		67.5
4	3	1.3	3	2.3	3	3.3
5	4	1.4	4	2.4	4	3.4
6	5	1.5	5	2.5	5	3.5
7	6	1.6	6	2.6	6	3.6
8	7	1.7	7	2.7	7	3.7
9	8	1.8	8	2.8	8	3.8
10	9	1.9	9	2.9	9	3.9
Sheet2

</SPAN>

Regards!

Yogi

 

[paliman]

I'm not rally sure if this is what you want, may be I did not understand your question correctly, but you can use the SUM function to sum various ranges.
Eg:
=SUM(A1:C3) to sum the whole range
=SUM(A1:A3,C1:C3) to sum two separated colums
=SUM(A1,B2,C3) to sum just these cells

Post back if this doesn't help

 

[imported_unknown]

Not what I meant exactly. The aim is to have a simpler formula than sum(b1,d1,f1,h1 etc etc) where there are large and variable numbers of columns to be added up. Sometimes extra columns are inserted so the formulae should be flexible enough to incorporate the relevant cell in each column.

Hope that makes more sense ...

Viv

 

[paliman]

Try to head all the columns in which you have data to sum with the same name. Then use the SUMIF function:

=SUMIF(A2:R2,"Value";B2:R2)

where you have headings in row #1 and data in row #2

 

[Aladin Akyurek]

On 2002-10-04 10:53, vhs wrote:
I need to sum alternatate columns in a large spreadsheet. e.g. Qty, Value, Qty, Value etc and I need to add all the values (which may be in columns headed Jan Feb Mar or other differing headings.

Is there a cunning trick?

Viv

=SUMPRODUCT((MOD(COLUMN(Range)-CELL("Col",Range)+0,2)=0)*(Range))

 

[Ekim]

Put this formula in L3. It will add B3, D3, F3, H3 and J3 i.e. alternative columns.

=SUMPRODUCT((MOD(COLUMN(B3:K3)-COLUMN(B3),2)=0)*B3:K3)

If one of the cells in the range contains text, the above formula will crash. So, if your range contains numbers and text, use this formula in cell L3 (array entered i.e. Shift-Ctlr-Enter)

=SUM(IF(MOD(COLUMN(B5:K5),2),,B5:K5))

 

[Aladin Akyurek]

On 2002-10-04 12:14, Ekim wrote:
Put this formula in L3. It will add B3, D3, F3, H3 and J3 i.e. alternative columns.

=SUMPRODUCT((MOD(COLUMN(B3:K3)-COLUMN(B3),2)=0)*B3:K3)

If one of the cells in the range contains text, the above formula will crash. So, if your range contains numbers and text, use this formula in cell L3 (array entered i.e. Shift-Ctlr-Enter)

=SUM(IF(MOD(COLUMN(B5:K5),2),,B5:K5))

See

http://www.mrexcel.com/board/viewtopic.php?topic=23503&forum=2

A note about crashing of SumProduct under the onditions that you specify...

Easily avoided by using

=SUMPRODUCT((MOD(COLUMN(Range)-CELL("Col",Range)+0,2)=0)+0,(Range))

that is, +0, instead of * between the (compound) condition and the range to sum.

Aladin

 

[Ekim]

Aladin,

Thanks for the tip.

Regards,

Mike

 

[imported_unknown]

Exactly what I needed. Many thanks

Viv