Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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+...? |
#2
|
|||
|
|||
=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+...? |
#3
|
|||
|
|||
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+...? |
#4
|
|||
|
|||
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+...? |
#5
|
|||
|
|||
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+...? |
#7
|
|||
|
|||
Quote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Skipping and protected cells | Excel Discussion (Misc queries) | |||
Auto Skipping and Protecting Worksheet | New Users to Excel | |||
skipping columns part 2 | Excel Worksheet Functions | |||
skipping columns | Excel Worksheet Functions | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) |