Re: Sum every second cell

[Jerry Meng <jmeng81@HOTMAIL.COM>]

Two variations to Dave's formula,


{=SUM((MOD(COLUMN(YourRange),2)=0)*YourRange)}   --- array formula

=SUMPRODUCT((MOD(COLUMN(YourRange),2)=0)*YourRange)


----- Original Message -----
From: "Wall, Peter" <Peter.Wall@SNCLAVALIN.COM>
To: <EXCEL-G@PEACH.EASE.LSOFT.COM>
Sent: Thursday, July 24, 2003 10:32 PM
Subject: Re: Sum every second cell


> Dave,
>
> Works a treat. Lots simpler than my array formula and trig function!
>
> As given and in your notes, adds even numbered columns.
>
> For Kylie's specific example with odd numbered columns obviously change
the
> "=0" to "=1".
>
> PW
>
> -----Original Message-----
> From: DAVE WILCOX [mailto:dwilcox01@HOTMAIL.COM]
> Sent: Friday, 25 July 2003 9:48 AM
> To: EXCEL-G@PEACH.EASE.LSOFT.COM
> Subject: Re: Sum every second cell
>
> Try this:
>
> =SUM(IF(MOD(COLUMN(YourRange),2)=0,YourRange))
>
> Notes:
> 1) This is range/array formula so you enter via CTRL+SHIFT+ENTER;
> 2) YourRange is, of course, your range to addup;
> 3) The number 2 will only work if and only ig the numbers are in even
> numbered columns!  If not use 3 but make sure there in every 3rd column.
>
> HTH
>
>
>
>
>
> >From: Kylie Manning <kamtech@OZEMAIL.COM.AU>
> >Reply-To: MS Excel General Q & A List <EXCEL-G@PEACH.EASE.LSOFT.COM>
> >To: EXCEL-G@PEACH.EASE.LSOFT.COM
> >Subject: Sum every second cell
> >Date: Fri, 25 Jul 2003 07:14:59 +1000
> >MIME-Version: 1.0
> >Received: from cherry.ease.lsoft.com ([209.119.0.109]) by
> >mc3-f7.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Thu, 24
Jul
> >2003 15:11:27 -0700
> >Received: from PEAR.EASE.LSOFT.COM (209.119.0.19) by
cherry.ease.lsoft.com
> >(LSMTP for Digital Unix v1.1b) with SMTP id
> ><12.00A969A8@cherry.ease.lsoft.com>; Thu, 24 Jul 2003 18:11:24 -0400
> >Received: from PEACH.EASE.LSOFT.COM by PEACH.EASE.LSOFT.COM
> >(LISTSERV-TCP/IP          release 1.8e) with spool id 49234326 for
> >EXCEL-G@PEACH.EASE.LSOFT.COM; Thu, 24 Jul 2003 17:23:18 -0400
> >Received: from 203.2.192.85 by WALNUT.EASE.LSOFT.COM (SMTPL release 1.0i)
> >with          TCP; Thu, 24 Jul 2003 17:13:18 -0400
> >Received: from kyliespc ([63.34.226.140]) by mta05.mail.mel.aone.net.au
> >with          ESMTP id
> ><20030724211313.OFRH20950.mta05.mail.mel.aone.net.au@kyliespc> for
> ><EXCEL-G@PEACH.EASE.LSOFT.COM>; Fri, 25 Jul 2003 07:13:13 +1000
> >X-Message-Info: N2mRlepP/as=
> >X-Priority: 3 (Normal)
> >X-MSMail-Priority: Normal
> >X-Mailer: Microsoft Outlook, Build 10.0.2627
> >Importance: Normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
> >Message-ID:  <000001c35228$a4d50820$9f3cfea9@kyliespc>
> >Sender:       MS Excel General Q & A List <EXCEL-G@PEACH.EASE.LSOFT.COM>
> >Precedence: list
> >Return-Path: owner-excel-g@PEACH.EASE.LSOFT.COM
> >X-OriginalArrivalTime: 24 Jul 2003 22:11:27.0275 (UTC)
> >FILETIME=[86F707B0:01C35230]
> >
> >Hello Everyone
> >
> >I was wondering if someone could please help.
> >
> >I need to create a sum function that will add the cell on row 3 for
> >every second column.
> >
> >eg
> >
> >=sum(C3,E3,G3,I3,K3 ....)
> >
> >As I have about 50 cells to add the manual method will take me forever
> >and risks errors.  Does anyone have a better way?
> >
> >Many thanks
> >Kylie
> >
> >
>
>--------------------------------------------------------------------------
> >The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
> >international's LISTSERV(R) software.  For subscription/signoff info
> >and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
> >                              COPYRIGHT INFO:
> >http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
> _________________________________________________________________
> Hotmail messages direct to your mobile phone
http://www.msn.co.uk/msnmobile
>
> --------------------------------------------------------------------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
> international's LISTSERV(R) software.  For subscription/signoff info
> and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
> --------------------------------------------------------------------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
> international's LISTSERV(R) software.  For subscription/signoff info
> and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>

--------------------------------------------------------------------------
The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
international's LISTSERV(R) software.  For subscription/signoff info
and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G