Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
Format cell in column B based on value in the next cell (column c) | Excel Discussion (Misc queries) | |||
how can i fill blank cells in column with abc while the right col. | Excel Discussion (Misc queries) | |||
Copying the contents of a column into a chart | Excel Worksheet Functions | |||
How to calculate the data in excel 2002 including only the last 9. | Excel Worksheet Functions |