adding every other column

G

gma

i need a formula in cell A1 to total cell C1 and everyother column after that
i.e. E1,G1 ect
i need a formula in cell B1 to total cell D1 and everyother column after
that i.e. F1,H1 ect
how do i do that?
 
D

David Biddulph

=SUMPRODUCT(MOD(COLUMN(C1:S1),2),C1:S1) in A1
=SUMPRODUCT(MOD(COLUMN(D1:T1)-1,2),D1:T1) in B1

Adjust the ranges to suit.
 
F

FSt1

hi
you are wanting to sum everyother column ie even numberd column and odd
numbered columns. your columns may have letters but excel can display them as
numbered columns. A,C,E,G etc are odd. B,D,F,H are even.
tools>options>general tab>check R1C1 reference style to see what i mean.
to sum odd columns, in A1 use this fomula.....
=SUMPRODUCT((MOD(COLUMN(C1:M1),2)=1)*(C1:M1))
adjust ranges to suit your data.
to sum even columns, in B1 use this formula.....
=SUMPRODUCT((MOD(COLUMN(D1:M1),2)=0)*(D1:M1))

regards
FSt1
 
G

gma

leave it up to me to screw up the question....the formula you gave me works
great but instead of me needing to add every other column, i need the formula
to add every third column. how do i do that?
 
R

Rick Rothstein

Try this...

=SUMPRODUCT((MOD(COLUMN(C1:M1),3)=0)*(C1:M1))

Adjust the M1's to whatever column is your maximum anticipated one to be
used.
 
T

T. Valko

To sum every 3rd cell starting from cell C1: (C1,F1,I1,L1,etc.)

=SUMPRODUCT(--(MOD(COLUMN(C1:T1)-COLUMN(C1),3)=0),C1:T1)

To sum every 3rd cell starting from cell D1: (D1,G1,J1,M1,etc.)

=SUMPRODUCT(--(MOD(COLUMN(D1:T1)-COLUMN(D1),3)=0),D1:T1)

In each formula adjust for the correct end of range T1.

Caveat: inserting new columns *within* the referenced range will cause the
formulas to calculate the incorrect cell interval.
 
G

gma

super...thanks!!!!

T. Valko said:
To sum every 3rd cell starting from cell C1: (C1,F1,I1,L1,etc.)

=SUMPRODUCT(--(MOD(COLUMN(C1:T1)-COLUMN(C1),3)=0),C1:T1)

To sum every 3rd cell starting from cell D1: (D1,G1,J1,M1,etc.)

=SUMPRODUCT(--(MOD(COLUMN(D1:T1)-COLUMN(D1),3)=0),D1:T1)

In each formula adjust for the correct end of range T1.

Caveat: inserting new columns *within* the referenced range will cause the
formulas to calculate the incorrect cell interval.

--
Biff
Microsoft Excel MVP





.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top