| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding every other column
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding every other column
=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. -- David Biddulph "gma" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding every other column
I'm on a new computer now; Office not installed yet so I can't try this and
show you how it works, but I assume you can see the samples here and replace the 'Row' with 'Column': http://www.mrexcel.com/td0066.html Or, look he http://www.mcgimpsey.com/excel/formu...meverynth.html -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "gma" wrote: 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding every other column
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. toolsoptionsgeneral tabcheck 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 "gma" wrote: 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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding every other column
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? "FSt1" wrote: 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. toolsoptionsgeneral tabcheck 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 "gma" wrote: 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding every other column
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. -- Rick (MVP - Excel) "gma" wrote in message ... 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? "FSt1" wrote: 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. toolsoptionsgeneral tabcheck 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 "gma" wrote: 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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding every other column
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 "gma" wrote in message ... 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? "FSt1" wrote: 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. toolsoptionsgeneral tabcheck 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 "gma" wrote: 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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding every other column
super...thanks!!!!
"T. Valko" wrote: 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 "gma" wrote in message ... 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? "FSt1" wrote: 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. toolsoptionsgeneral tabcheck 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 "gma" wrote: 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? . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding every other column
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "gma" wrote in message ... super...thanks!!!! "T. Valko" wrote: 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 "gma" wrote in message ... 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? "FSt1" wrote: 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. toolsoptionsgeneral tabcheck 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 "gma" wrote: 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? . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removing one column of data and adding another column | Excel Discussion (Misc queries) | |||
| Column Chart - Adding text in the column in Excel 2007 | Charts and Charting in Excel | |||
| Excel 2000: Adding column D based on value in column A | Excel Worksheet Functions | |||
| Adding a Custom Column based on an existing Column (EXPERT) | Excel Worksheet Functions | |||
| adding numbers from column B, while condition on column A | Excel Discussion (Misc queries) | |||
Linear Mode
