adding every other column
in [Functions]
|
From: Rick Rothstein on 24 Dec 2009 16:42 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" <gma(a)mircosoft.com> wrote in message news:C447DD73-D1F6-4C05-AC4F-F63BA94646AC(a)microsoft.com... > 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. >> 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 >> >> "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?
From: T. Valko on 24 Dec 2009 16:46 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" <gma(a)mircosoft.com> wrote in message news:C447DD73-D1F6-4C05-AC4F-F63BA94646AC(a)microsoft.com... > 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. >> 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 >> >> "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?
From: gma on 26 Dec 2009 14:15 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" <gma(a)mircosoft.com> wrote in message > news:C447DD73-D1F6-4C05-AC4F-F63BA94646AC(a)microsoft.com... > > 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. > >> 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 > >> > >> "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? > > > . >
From: T. Valko on 26 Dec 2009 16:20 You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "gma" <gma(a)mircosoft.com> wrote in message news:62FEADDA-7E6A-4A0C-BBCA-F9881C40BA3A(a)microsoft.com... > 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" <gma(a)mircosoft.com> wrote in message >> news:C447DD73-D1F6-4C05-AC4F-F63BA94646AC(a)microsoft.com... >> > 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. >> >> 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 >> >> >> >> "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? >> >> >> . >>
First
|
Prev
|
Pages: 1 2 Prev: Please help me to solve the below equation in excel Next: Multiple If Function |