Sum skipping columns
 

In cell DC9 I want to enter a formula that will give me the sum of
E9+H9+K9+N9+...(every third column in row 9). Is this possible without
having to type out =E9+H9+...?

=SUMPRODUCT(C9:N9,--(MOD(COLUMN(C9:N9)-2,3)=0))

"Steve" wrote:

In cell DC9 I want to enter a formula that will give me the sum of
E9+H9+K9+N9+...(every third column in row 9). Is this possible without
having to type out =E9+H9+...?

Oops-

=SUMPRODUCT(E9:Z9,--(MOD(COLUMN(E9:Z9)-2,3)=0))

"Duke Carey" wrote:

=SUMPRODUCT(C9:N9,--(MOD(COLUMN(C9:N9)-2,3)=0))

"Steve" wrote:

In cell DC9 I want to enter a formula that will give me the sum of
E9+H9+K9+N9+...(every third column in row 9). Is this possible without
having to type out =E9+H9+...?

try this for row 4. correct for word wrap
=SUMPRODUCT((MOD(COLUMN(E4:INDEX(4:4,100))-CELL("Col",E4)+0,3)=0)*(E4:INDEX(
4:4,100)))
--
Don Guillett
SalesAid Software

"Steve" wrote in message
...
In cell DC9 I want to enter a formula that will give me the sum of
E9+H9+K9+N9+...(every third column in row 9). Is this possible without
having to type out =E9+H9+...?

Worked perfectly. Thanks...huge timesaver.

"Duke Carey" wrote:

Oops-

=SUMPRODUCT(E9:Z9,--(MOD(COLUMN(E9:Z9)-2,3)=0))

"Duke Carey" wrote:

=SUMPRODUCT(C9:N9,--(MOD(COLUMN(C9:N9)-2,3)=0))

"Steve" wrote:

In cell DC9 I want to enter a formula that will give me the sum of
E9+H9+K9+N9+...(every third column in row 9). Is this possible without
having to type out =E9+H9+...?

Nice

--
Don Guillett
SalesAid Software

"Duke Carey" wrote in message
...
Oops-

=SUMPRODUCT(E9:Z9,--(MOD(COLUMN(E9:Z9)-2,3)=0))

"Duke Carey" wrote:

=SUMPRODUCT(C9:N9,--(MOD(COLUMN(C9:N9)-2,3)=0))

"Steve" wrote:

In cell DC9 I want to enter a formula that will give me the sum of
E9+H9+K9+N9+...(every third column in row 9). Is this possible
without
having to type out =E9+H9+...?

This saved another person after 7 years: Me.

Can anyone tell me why it's -2,3 in the MOD function? I can't figure it out.

I use the formula for column B2 to CZ2. I skip 1 column. (so summarizing B, D, F, H, and so on.) I used -1,2 instead of the -2,3 from the previous posting. But it worked, the results seem to be correct. But only column B2 to E2 have data and i don't know if it will be correct when more data will come. That's why i need to know what the -2,3 stands for in the old example.