SUM every other column
 

I have a large table where I would like to get the sum of
every other column. My current method is to use the +
key and click on every other cell.

Is there a better way?

Thanks,
JC

Something like this may work for you:

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

(first formula gives even numbered columns, second gives odd numbered columns)

JC wrote:

I have a large table where I would like to get the sum of
every other column. My current method is to use the +
key and click on every other cell.

Is there a better way?

Thanks,
JC

--

Dave Peterson

Works like a charm. Thanks Dave.

JC
-----Original Message-----
Something like this may work for you:

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

(first formula gives even numbered columns, second gives
odd numbered columns)

JC wrote:

I have a large table where I would like to get the sum
of
every other column. My current method is to use the +
key and click on every other cell.

Is there a better way?

Thanks,
JC

--

Dave Peterson
.

I don't think I like this formula--even though it worked ok for you.

If you insert a single column in that range, then your formula won't sum the
columns that you want. (It's happened to a workbook I shared with a co-worker.
He inserted an extra column to add a short description--it screwed up his
results.)

I think I'd add a header row (row 1???). Then put an indicator to add those
cells that have the indicator in it.

=sumproduct(--(a1:k1="x"),a2:k2)

Then if you insert/delete columns, it might be less work.

JC wrote:

Works like a charm. Thanks Dave.

JC
-----Original Message-----
Something like this may work for you:

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

(first formula gives even numbered columns, second gives
odd numbered columns)

JC wrote:

I have a large table where I would like to get the sum
of
every other column. My current method is to use the +
key and click on every other cell.

Is there a better way?

Thanks,
JC

--

Dave Peterson
.


--

Dave Peterson

Wouldn't a SUMIF work just as well and probably be faster?

"Dave Peterson" wrote:

I don't think I like this formula--even though it worked ok for you.

If you insert a single column in that range, then your formula won't sum the
columns that you want. (It's happened to a workbook I shared with a co-worker.
He inserted an extra column to add a short description--it screwed up his
results.)

I think I'd add a header row (row 1???). Then put an indicator to add those
cells that have the indicator in it.

=sumproduct(--(a1:k1="x"),a2:k2)

Then if you insert/delete columns, it might be less work.

JC wrote:

Works like a charm. Thanks Dave.

JC
-----Original Message-----
Something like this may work for you:

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

(first formula gives even numbered columns, second gives
odd numbered columns)

JC wrote:

I have a large table where I would like to get the sum
of
every other column. My current method is to use the +
key and click on every other cell.

Is there a better way?

Thanks,
JC

--

Dave Peterson
.


--

Dave Peterson

Yep.



Jonathan Cooper wrote:

Wouldn't a SUMIF work just as well and probably be faster?

"Dave Peterson" wrote:

I don't think I like this formula--even though it worked ok for you.

If you insert a single column in that range, then your formula won't sum the
columns that you want. (It's happened to a workbook I shared with a co-worker.
He inserted an extra column to add a short description--it screwed up his
results.)

I think I'd add a header row (row 1???). Then put an indicator to add those
cells that have the indicator in it.

=sumproduct(--(a1:k1="x"),a2:k2)

Then if you insert/delete columns, it might be less work.

JC wrote:

Works like a charm. Thanks Dave.

JC
-----Original Message-----
Something like this may work for you:

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

(first formula gives even numbered columns, second gives
odd numbered columns)

JC wrote:

I have a large table where I would like to get the sum
of
every other column. My current method is to use the +
key and click on every other cell.

Is there a better way?

Thanks,
JC

--

Dave Peterson
.


--

Dave Peterson


--

Dave Peterson