Formula for sum of alternate cells

John Blackwell

unread,

Sep 25, 2008, 5:29:00 AM9/25/08

to

Folks,

I'm trying to find a formula for summing the contents of alternate cells in
a row?

I have a large workbook and want to calculate the contents of row 23 - i.e.
E23, G23, I23, K23, M23 etc etc, up to KK23. Is there a simple formula for
this?

John Blackwell

Mike H

unread,

Sep 25, 2008, 5:41:01 AM9/25/08

to

John,

=SUMPRODUCT(--(MOD(COLUMN(E23:KK23),2)=1),E23:KK23)

Mike

Ashish Mathur

unread,

Sep 25, 2008, 5:44:53 AM9/25/08

to

Hi,

Please array enter (Ctrl+Shift+Enter) the following formula

SUM(IF((MOD(COLUMN(E23:I23),2)<>0),E23:I23))

What this formula does is that if the column number/2<>0, then it sums up
the numbers from those columns.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"John Blackwell" <JohnBl...@discussions.microsoft.com> wrote in message
news:BCE39331-0E92-47D5...@microsoft.com...

John Blackwell

unread,

Sep 25, 2008, 6:06:13 AM9/25/08

to

Fantastic Mike - thank you.

Sorry to be a pain, but how do I sum the other cells - i.e. D23, F23, H23,
J23, L23 etc etc, up to KK23?

John

Mike H

unread,

Sep 25, 2008, 6:13:01 AM9/25/08

to

Hi,

Glad I could help for d23 etc use this
=SUMPRODUCT(--(MOD(COLUMN(D23:KK23),2)=0),D23:KK23)

Note all Ive done is change the range and altered the modulus to zero.
Columns D, F etc are even numbered so for D the formula days 2 mod 2 = 0

Mike

HARSHAWARDHAN. S .SHASTRI

unread,

Sep 25, 2008, 10:31:01 AM9/25/08

to

Mike,

What is the significance of -- in formula.

Harshawardhan Shastri

David Biddulph

unread,

Sep 25, 2008, 12:59:02 PM9/25/08

to

For an explanation of the double unary minus, see
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
--
David Biddulph

"HARSHAWARDHAN. S .SHASTRI"
<HARSHAWARD...@discussions.microsoft.com> wrote in message
news:D133C321-EE4F-4DA3...@microsoft.com...

Lori

unread,

Sep 25, 2008, 1:06:04 PM9/25/08

to

You could also try these two:

=SUM(NPV({0,-2},D23:K23))/2
=SUM(NPV({0,-2},,D23:K23))/2

Peo Sjoblom

unread,

Sep 25, 2008, 1:17:01 PM9/25/08

to

Clever. How do you find these Lori?

--

Regards,

Peo Sjoblom

"Lori" <Lo...@discussions.microsoft.com> wrote in message
news:6A4B6D28-9088-47D5...@microsoft.com...

Lori

unread,

Sep 25, 2008, 3:49:01 PM9/25/08

to

Thanks Peo, i make them up :-) i figure there are many functions out there
which can be put to good use as long as you're aware of the limitations.

NPV is a good example - other useful values for rate are:
1/-0.5: for a binary sum
9/-0.9 : for a decimal sum
Big/small: for the first or last value

eg: =NPV(1e20,A1:K1)*1e20

returns the first non-blank value in the row. Or...in Excel 2007 (cse):

=NPV(-0.9,,IFERROR(MID(A1,51-ROW(1:50),1)%,""))&""

extracts the numeric portion of a string such as
"apple 123 banana 345 pear 567 orange 678" -> 123345567678

Peo Sjoblom

unread,

Sep 25, 2008, 4:28:04 PM9/25/08

to

Thanks for the info Lori, very interesting.

I love the last one and I know how to get
numbers from a string as long as they are in one place.

--

Regards,

Peo Sjoblom

"Lori" <Lo...@discussions.microsoft.com> wrote in message

news:03207FA3-2841-4848...@microsoft.com...

Punnoose Mammen

unread,

Jul 26, 2010, 2:52:29 AM7/26/10

to

Can someone please help me with a excel work sheet formula for sum total of even number cells if the odd number cells value is 1 or 0.
Example:1 if a1=1,a3=1,a5=0,a7=1,a9=0,a11=1, then a2+a4+a8+a12

Example:2 if a1=1,a3=1,a5=0,a7=1,a9=0,a11=1, then a6+a10

Mike wrote:

Hi,Glad I could help for d23 etc use
25-Sep-08

Hi,

Glad I could help for d23 etc use this
=SUMPRODUCT(--(MOD(COLUMN(D23:KK23),2)=0),D23:KK23)

Note all Ive done is change the range and altered the modulus to zero.
Columns D, F etc are even numbered so for D the formula days 2 mod 2 = 0

Mike

"John Blackwell" wrote:

Previous Posts In This Thread:

On Thursday, September 25, 2008 5:29 AM
JohnBlackwel wrote:

Formula for sum of alternate cells
Folks,

I'm trying to find a formula for summing the contents of alternate cells in
a row?

I have a large workbook and want to calculate the contents of row 23 - i.e.
E23, G23, I23, K23, M23 etc etc, up to KK23. Is there a simple formula for
this?

John Blackwell

On Thursday, September 25, 2008 5:41 AM
Mike wrote:

RE: Formula for sum of alternate cells
John,

=SUMPRODUCT(--(MOD(COLUMN(E23:KK23),2)=1),E23:KK23)

Mike

"John Blackwell" wrote:

On Thursday, September 25, 2008 5:44 AM
Ashish Mathur wrote:

Hi,Please array enter (Ctrl+Shift+Enter) the following
Hi,

Please array enter (Ctrl+Shift+Enter) the following formula

SUM(IF((MOD(COLUMN(E23:I23),2)<>0),E23:I23))

What this formula does is that if the column number/2<>0, then it sums up
the numbers from those columns.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"John Blackwell" <JohnBl...@discussions.microsoft.com> wrote in message
news:BCE39331-0E92-47D5...@microsoft.com...

On Thursday, September 25, 2008 6:06 AM
JohnBlackwel wrote:

Fantastic Mike - thank you.
Fantastic Mike - thank you.

Sorry to be a pain, but how do I sum the other cells - i.e. D23, F23, H23,
J23, L23 etc etc, up to KK23?

John

"Mike H" wrote:

On Thursday, September 25, 2008 6:13 AM
Mike wrote:

Hi,Glad I could help for d23 etc use
Hi,

Glad I could help for d23 etc use this
=SUMPRODUCT(--(MOD(COLUMN(D23:KK23),2)=0),D23:KK23)

Note all Ive done is change the range and altered the modulus to zero.
Columns D, F etc are even numbered so for D the formula days 2 mod 2 = 0

Mike

"John Blackwell" wrote:

On Thursday, September 25, 2008 10:31 AM
HARSHAWARDHANSSHASTR wrote:

Mike, What is the significance of -- in formula.
Mike,

What is the significance of -- in formula.

Harshawardhan Shastri

"Mike H" wrote:

On Thursday, September 25, 2008 12:59 PM
David Biddulph wrote:

Re: Formula for sum of alternate cells

For an explanation of the double unary minus, see
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
--
David Biddulph

"HARSHAWARDHAN. S .SHASTRI"

On Thursday, September 25, 2008 1:06 PM
Lor wrote:

You could also try these
You could also try these two:

=SUM(NPV({0,-2},D23:K23))/2
=SUM(NPV({0,-2},,D23:K23))/2

"John Blackwell" wrote:

On Thursday, September 25, 2008 1:17 PM
Peo Sjoblom wrote:

Clever. How do you find these Lori?
Clever. How do you find these Lori?

--

Regards,

Peo Sjoblom

On Thursday, September 25, 2008 3:49 PM
Lor wrote:

Re: Formula for sum of alternate cells

Thanks Peo, i make them up :-) i figure there are many functions out there
which can be put to good use as long as you're aware of the limitations.

NPV is a good example - other useful values for rate are:
1/-0.5: for a binary sum
9/-0.9 : for a decimal sum
Big/small: for the first or last value

eg: =NPV(1e20,A1:K1)*1e20

returns the first non-blank value in the row. Or...in Excel 2007 (cse):

=NPV(-0.9,,IFERROR(MID(A1,51-ROW(1:50),1)%,""))&""

extracts the numeric portion of a string such as
"apple 123 banana 345 pear 567 orange 678" -> 123345567678

"Peo Sjoblom" wrote:

On Thursday, September 25, 2008 4:28 PM
Peo Sjoblom wrote:

Thanks for the info Lori, very interesting.
Thanks for the info Lori, very interesting.

I love the last one and I know how to get
numbers from a string as long as they are in one place.

--

Regards,

Peo Sjoblom

Submitted via EggHeadCafe - Software Developer Portal of Choice
Six Free Visual Studio 2010 MSDN Memberships Giveaway
http://www.eggheadcafe.com/tutorials/aspnet/f7338bb9-7fa4-4fa8-9e5a-244857b0d9d4/six-free-visual-studio-2010-msdn-memberships-giveaway.aspx

Pete_UK

unread,

Jul 26, 2010, 4:18:01 AM7/26/10

to

Try this:

=SUMPRODUCT((MOD(ROW(A1:A11),2)=1)*(A1:A11=1),A2:A12)

for the odd-numbered cells being 1, and this:

=SUMPRODUCT((MOD(ROW(A1:A11),2)=1)*(A1:A11=0),A2:A12)

for the odd-numbered cells being zero. Note that the final range is
offset from the other ranges, but is the same size.

Hope this helps.

Pete

Vibhor Bansal

unread,

Mar 23, 2011, 7:25:53 AM3/23/11

to

Hi Mike,

This formula help me also for sum of the products at alternate columns. I just wanna know the impact of '--' in a formula for better understanding...

Thnx,

Vibhor

> On Thursday, September 25, 2008 5:29 AM JohnBlackwel wrote:

> Folks,
>
> I'm trying to find a formula for summing the contents of alternate cells in
> a row?
>
> I have a large workbook and want to calculate the contents of row 23 - i.e.
> E23, G23, I23, K23, M23 etc etc, up to KK23. Is there a simple formula for
> this?
>
> John Blackwell

>> On Thursday, September 25, 2008 5:41 AM Mike wrote:

>> John,
>>
>> =SUMPRODUCT(--(MOD(COLUMN(E23:KK23),2)=1),E23:KK23)
>>
>> Mike
>>
>> "John Blackwell" wrote:

>>> On Thursday, September 25, 2008 5:44 AM Ashish Mathur wrote:

>>> Hi,
>>>
>>> Please array enter (Ctrl+Shift+Enter) the following formula
>>>
>>> SUM(IF((MOD(COLUMN(E23:I23),2)<>0),E23:I23))
>>>
>>> What this formula does is that if the column number/2<>0, then it sums up
>>> the numbers from those columns.
>>>
>>>
>>> --
>>> Regards,
>>>
>>> Ashsih Mathur
>>> Microsoft Excel MVP
>>> www.ashishmathur.com
>>>
>>> "John Blackwell" <JohnBl...@discussions.microsoft.com> wrote in message
>>> news:BCE39331-0E92-47D5...@microsoft.com...

>>>> On Thursday, September 25, 2008 6:06 AM JohnBlackwel wrote:

>>>> Fantastic Mike - thank you.
>>>>

>>>> Sorry to be a pain, but how do I sum the other cells - i.e. D23, F23, H23,
>>>> J23, L23 etc etc, up to KK23?
>>>>
>>>> John
>>>>
>>>> "Mike H" wrote:

>>>>> On Thursday, September 25, 2008 6:13 AM Mike wrote:

>>>>> Hi,
>>>>>
>>>>> Glad I could help for d23 etc use this
>>>>> =SUMPRODUCT(--(MOD(COLUMN(D23:KK23),2)=0),D23:KK23)
>>>>>
>>>>> Note all Ive done is change the range and altered the modulus to zero.
>>>>> Columns D, F etc are even numbered so for D the formula days 2 mod 2 = 0
>>>>>
>>>>> Mike
>>>>>
>>>>> "John Blackwell" wrote:

>>>>>> On Thursday, September 25, 2008 10:31 AM HARSHAWARDHANSSHASTR wrote:

>>>>>> Mike,
>>>>>>
>>>>>> What is the significance of -- in formula.
>>>>>>

>>>>>> Harshawardhan Shastri
>>>>>>
>>>>>> "Mike H" wrote:

>>>>>>> On Thursday, September 25, 2008 12:59 PM David Biddulph wrote:

>>>>>>> For an explanation of the double unary minus, see
>>>>>>> http://www.mcgimpsey.com/excel/formulae/doubleneg.html
>>>>>>> --
>>>>>>> David Biddulph
>>>>>>>
>>>>>>> "HARSHAWARDHAN. S .SHASTRI"

>>>>>>>> On Thursday, September 25, 2008 1:06 PM Lor wrote:

>>>>>>>> You could also try these two:
>>>>>>>>
>>>>>>>> =SUM(NPV({0,-2},D23:K23))/2
>>>>>>>> =SUM(NPV({0,-2},,D23:K23))/2
>>>>>>>>
>>>>>>>> "John Blackwell" wrote:

>>>>>>>>> On Thursday, September 25, 2008 1:17 PM Peo Sjoblom wrote:

>>>>>>>>> Clever. How do you find these Lori?
>>>>>>>>>

>>>>>>>>> --
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Regards,
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Peo Sjoblom

>>>>>>>>>> On Thursday, September 25, 2008 3:49 PM Lor wrote:

>>>>>>>>>> Thanks Peo, i make them up :-) i figure there are many functions out there
>>>>>>>>>> which can be put to good use as long as you're aware of the limitations.
>>>>>>>>>>
>>>>>>>>>> NPV is a good example - other useful values for rate are:
>>>>>>>>>> 1/-0.5: for a binary sum
>>>>>>>>>> 9/-0.9 : for a decimal sum
>>>>>>>>>> Big/small: for the first or last value
>>>>>>>>>>
>>>>>>>>>> eg: =NPV(1e20,A1:K1)*1e20
>>>>>>>>>>
>>>>>>>>>> returns the first non-blank value in the row. Or...in Excel 2007 (cse):
>>>>>>>>>>
>>>>>>>>>> =NPV(-0.9,,IFERROR(MID(A1,51-ROW(1:50),1)%,""))&""
>>>>>>>>>>
>>>>>>>>>> extracts the numeric portion of a string such as
>>>>>>>>>> "apple 123 banana 345 pear 567 orange 678" -> 123345567678
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> "Peo Sjoblom" wrote:

>>>>>>>>>>> On Thursday, September 25, 2008 4:28 PM Peo Sjoblom wrote:

>>>>>>>>>>> Thanks for the info Lori, very interesting.
>>>>>>>>>>>

>>>>>>>>>>> I love the last one and I know how to get
>>>>>>>>>>> numbers from a string as long as they are in one place.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Regards,
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Peo Sjoblom

Gord Dibben

unread,

Mar 23, 2011, 11:35:46 AM3/23/11

to

Have a read at JE McGimpsey's site for usage of double unary.

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Gord Dibben MS Excel MVP

joeu2004

unread,

Mar 23, 2011, 1:22:41 PM3/23/11

to

On Mar 23, 4:25 am, Vibhor Bansal <vibhor.ban...@gmail.com> wrote:
> This formula help me also for sum of the products at
> alternate columns. I just wanna know the impact of '--'
> in a formula for better understanding.

Presumably you are asking about a formula like:

=SUMPRODUCT(--(MOD(COLUMN(D23:KK23),2)=0),D23:KK23)

The double-negative (--) converts TRUE and FALSE to 1 and 0, which
SUMPRODUCT requires to be effective in this context.

Any arithmetic operation would do the same thing. For that reason,
some people prefer to multiply by 1 (1*) instead of using double-
negative.

Also, for example:

=SUMPRODUCT((A1:100>3)*(A1:A100<=7))

counts the number of cells in A1:A100 that meets both conditions. The
multiply (*) acts like AND; we cannot use AND in this context.

No need for double-negative (--) in that context, although the
following is equivalent:

=SUMPRODUCT(--(A1:100>3),--(A1:A100<=7))

Basically, use double-negative when there is no other arithmetic
operations that would convert TRUE and FALSE to 1 and 0.

basu....@gmail.com

unread,

Sep 26, 2014, 3:10:16 AM9/26/14

to

On Thursday, September 25, 2008 2:59:00 PM UTC+5:30, John Blackwell wrote:
> Folks,I'm trying to find a formula for summing the contents of alternate cells in a row?I have a large workbook and want to calculate the contents of row 23 - i.e. E23, G23, I23, K23, M23 etc etc, up to KK23. Is there a simple formula for this?John Blackwell
Jan Jan Feb Feb Total Total
Sales Type 2013 2014 2013 2014 2013 2014
A 362 762 512 932 874 1,694
B 407 751 834 427 1,241 1,178
A 311 694 519 778 830 1,472
B 714 484 697 478 1,411 962
A 281 952 548 503 829 1,455
B 648 527 398 567 1,046 1,094
A 476 947 161 287 637 1,234
B 526 801 966 896 1,492 1,697
A 556 235 267 217 823 452
B 102 168 728 621 830 789
A Total 2,397 2,731 3,623 2,989 6,020 5,720
B Total 1,986 3,590 2,007 2,717 3,993 6,307

For Column Total of 2013: =SUM(IF((1-MOD(COLUMN($D22:$G22),2)<>0),$D22:$G22))
For Column Total of 2014: =SUM(IF((MOD(COLUMN($D22:$G22),2)<>0),$D22:$G22))
For Alternate Row Total of ROW of A for 2013: =SUM(IF((MOD(ROW(D$22:D$31),2)<>0),D$22:D$31))
For Alternate Row Total of ROW of B for 2013: =SUM(IF((MOD(1+ROW(D$22:D$31),2)<>0),D$22:D$31))

Copy the formula and define the range as desired and then press "CTRL+SHIFT+ENTER"

Hope this is fine.