Formula for sum of alternate cells

  • Thread starter Thread starter John Blackwell
  • Start date Start date
J

John Blackwell

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
 
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
 
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
 
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
 
Mike,

What is the significance of -- in formula.

Harshawardhan Shastri
 
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
 
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
 
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 thi
=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 =

Mik

:

Previous Posts In This Thread:

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

RE: Formula for sum of alternate cells
John

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

Mik

:

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

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

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 Mathu
Microsoft Excel MV
www.ashishmathur.co


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

Joh

:

Hi,Glad I could help for d23 etc use
Hi

Glad I could help for d23 etc use thi
=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 =

Mik

:

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

What is the significance of -- in formula

Harshawardhan Shastr

:

Re: Formula for sum of alternate cells
For an explanation of the double unary minus, se
http://www.mcgimpsey.com/excel/formulae/doubleneg.htm
-
David Biddulp

"HARSHAWARDHAN. S .SHASTRI"

You could also try these
You could also try these two

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

:

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

-

Regards

Peo Sjoblom

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 su
9/-0.9 : for a decimal su
Big/small: for the first or last valu

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

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 a
"apple 123 banana 345 pear 567 orange 678" -> 12334556767


:

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

I love the last one and I know how to ge
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/tutorial...al-studio-2010-msdn-memberships-giveaway.aspx
 
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
 
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
 
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.
Click to expand...

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.
 
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
Click to expand...
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.
 
Back
Top