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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,750
Messages
6,138,406
Members
450,134
Latest member
TYoung24

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
Back
Top