Summing Alternate Columns

imported_unknown

Active Member
Joined
Jan 13, 2002
Messages
424
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDEFGH
1QtyJanValueQtyFebValueQtyMarValueSumofValueColumns
211.112.113.1
321.222.223.267.5
431.332.333.3
541.442.443.4
651.552.553.5
761.662.663.6
871.772.773.7
981.882.883.8
1091.992.993.9
Sheet2
</SPAN>

Regards!

Yogi
 

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
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

Active Member
Joined
Jan 13, 2002
Messages
424
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

Active Member
Joined
Jul 7, 2002
Messages
254

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
Jun 30, 2002
Messages
1,416

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,599
Messages
5,597,105
Members
414,125
Latest member
iQQ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top