# Summing Alternate Columns

#### imported_unknown

##### Active Member
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

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

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

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

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))

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))

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.

Thanks for the tip.

Regards,

Mike

Exactly what I needed. Many thanks

Viv

Replies
5
Views
109
Replies
2
Views
422
Replies
8
Views
280
Replies
1
Views
89
Replies
2
Views
326

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.

### Which adblocker are you using?

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

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