SUM Every Other Column

[Tarig]

Hi,

Can someone please help me with a formula that sums numbers from every other
column i.e. B1, D1, F1...etc?

 

[Luke M]

For row 1:

=SUMPRODUCT(--(MOD(COLUMN(1:1),2)=0),1:1)

Change the range "1:1" to something smaller in both callouts if you desire a
smaller range. If you want the odd columns (A, C, etc) change the "=0" to
"=1".

 

[Bernard Liengme]

But full column/row reference such as A:A or 1:1 are permit in SUMPRODUCT
only in Excel 2007 and 2010. In earlier versions you would need ranges
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=0),A1:Z1)
best wishes

 

[RagDyeR]

I think you'll find Bernard, that the restriction for array formulas *and*
Sumproduct apply *only* to ROWS in the earlier versions.

XL handled the 256 columns without a hitch.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

But full column/row reference such as A:A or 1:1 are permit in SUMPRODUCT
only in Excel 2007 and 2010. In earlier versions you would need ranges
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=0),A1:Z1)
best wishes

 

[Bernard Liengme]

Thanks for the "heads up". I am so use to tabular data (columns of data)
that I have never had need to try =SUMPRODUCT(--(1:1="a")) which does work
in XL 2003 as you said.

But =SUMPRODUCT(--(A:A="a")) did not - gave #NUM! error
I have learnt something today, thanks again
best wishes