Add cells on two adjacent rows but non-adjacent columns

Hello,

Thank you for helping, as I am not too familiar with the advanced funtions
of Excel.

I am working with a very large worksheet and would like to add cells
together. I would like to add two cells (on two adjacent rows but same
column) every 7 columns apart.


eg.
A B C D E
F G
1 Days Date Value XAO Return XAO
Return AR
2 -2 02/12/2008 0.042 3437 0.1333 -0.023
-0.454
3 -1 03/12/2008 0.043 3427 0.083 -0.987
-0.587
4 0 04/12/2008 0.477 3553 -0.546 0.456
0.623
5 +1 05/12/2008 0.567 3438 0.9865 0.387
0.218

I would like to add cells G3:G4 for every one of my stocks as I want to
calculate the AR for each. Each of the stocks has the same format, and
therefore, in-between the AR column for each of the stocks is exactly 7
columns.

Hopefully, I would like to put the new values into a new column, so I can
just auto-fill down.

Thank you again. This is my first time posting here, I hope I have made my
question clear enough.

Hi,

This formula will sum up the two adjacent cells every 7 columns apart

=SUMPRODUCT((MOD((COLUMN(B3:U3)-COLUMN($A$3)),9)={1;2})*(B3:U3))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hello,

Thank you for helping, as I am not too familiar with the advanced funtions
of Excel.

I am working with a very large worksheet and would like to add cells
together. I would like to add two cells (on two adjacent rows but same
column) every 7 columns apart.


eg.
A B C D E
F G
1 Days Date Value XAO Return XAO
Return AR
2 -2 02/12/2008 0.042 3437 0.1333 -0.023
-0.454
3 -1 03/12/2008 0.043 3427 0.083 -0.987
-0.587
4 0 04/12/2008 0.477 3553 -0.546 0.456
0.623
5 +1 05/12/2008 0.567 3438 0.9865 0.387
0.218

I would like to add cells G3:G4 for every one of my stocks as I want to
calculate the AR for each. Each of the stocks has the same format, and
therefore, in-between the AR column for each of the stocks is exactly 7
columns.

Hopefully, I would like to put the new values into a new column, so I can
just auto-fill down.

Thank you again. This is my first time posting here, I hope I have made my
question clear enough.

Hi,

I think the alignment of the example made it quite confusing. I need to add
up cells in the same row which are spaced 7 cells apart exactly.

But thank you for your help. If you could state a general formula, then I
can try to fit in my own data.

Thank you.



"Ashish Mathur" wrote:

Hi,

This formula will sum up the two adjacent cells every 7 columns apart

=SUMPRODUCT((MOD((COLUMN(B3:U3)-COLUMN($A$3)),9)={1;2})*(B3:U3))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hello,

Thank you for helping, as I am not too familiar with the advanced funtions
of Excel.

I am working with a very large worksheet and would like to add cells
together. I would like to add two cells (on two adjacent rows but same
column) every 7 columns apart.


eg.
A B C D E
F G
1 Days Date Value XAO Return XAO
Return AR
2 -2 02/12/2008 0.042 3437 0.1333 -0.023
-0.454
3 -1 03/12/2008 0.043 3427 0.083 -0.987
-0.587
4 0 04/12/2008 0.477 3553 -0.546 0.456
0.623
5 +1 05/12/2008 0.567 3438 0.9865 0.387
0.218

I would like to add cells G3:G4 for every one of my stocks as I want to
calculate the AR for each. Each of the stocks has the same format, and
therefore, in-between the AR column for each of the stocks is exactly 7
columns.

Hopefully, I would like to put the new values into a new column, so I can
just auto-fill down.

Thank you again. This is my first time posting here, I hope I have made my
question clear enough.

Hi,

OK, help me understand this better. If your data is in B3:U3, then what do
you want to do:

1. Add B3, J3, R3 etc ;or
2. B3,C3,J3,K3,R3,S3

Please clarify

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

I think the alignment of the example made it quite confusing. I need to
add
up cells in the same row which are spaced 7 cells apart exactly.

But thank you for your help. If you could state a general formula, then I
can try to fit in my own data.

Thank you.



"Ashish Mathur" wrote:

Hi,

This formula will sum up the two adjacent cells every 7 columns apart

=SUMPRODUCT((MOD((COLUMN(B3:U3)-COLUMN($A$3)),9)={1;2})*(B3:U3))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hello,

Thank you for helping, as I am not too familiar with the advanced
funtions
of Excel.

I am working with a very large worksheet and would like to add cells
together. I would like to add two cells (on two adjacent rows but same
column) every 7 columns apart.


eg.
A B C D E
F G
1 Days Date Value XAO Return XAO
Return AR
2 -2 02/12/2008 0.042 3437
0.1333 -0.023
-0.454
3 -1 03/12/2008 0.043 3427
083 -0.987
-0.587
4 0 04/12/2008 0.477 3553 -0.546
0.456
0.623
5 +1 05/12/2008 0.567 3438 0.9865
0.387
0.218

I would like to add cells G3:G4 for every one of my stocks as I want to
calculate the AR for each. Each of the stocks has the same format, and
therefore, in-between the AR column for each of the stocks is exactly 7
columns.

Hopefully, I would like to put the new values into a new column, so I
can
just auto-fill down.

Thank you again. This is my first time posting here, I hope I have made
my
question clear enough.

Hi,

The cells I want to add up start from G4, then N4, U4... etc and the last
cell is BCZ4.

Actually, there is only 6 cells in between each cell I want to add up.

Thanks


"Ashish Mathur" wrote:

Hi,

OK, help me understand this better. If your data is in B3:U3, then what do
you want to do:

1. Add B3, J3, R3 etc ;or
2. B3,C3,J3,K3,R3,S3

Please clarify

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

I think the alignment of the example made it quite confusing. I need to
add
up cells in the same row which are spaced 7 cells apart exactly.

But thank you for your help. If you could state a general formula, then I
can try to fit in my own data.

Thank you.



"Ashish Mathur" wrote:

Hi,

This formula will sum up the two adjacent cells every 7 columns apart

=SUMPRODUCT((MOD((COLUMN(B3:U3)-COLUMN($A$3)),9)={1;2})*(B3:U3))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hello,

Thank you for helping, as I am not too familiar with the advanced
funtions
of Excel.

I am working with a very large worksheet and would like to add cells
together. I would like to add two cells (on two adjacent rows but same
column) every 7 columns apart.


eg.
A B C D E
F G
1 Days Date Value XAO Return XAO
Return AR
2 -2 02/12/2008 0.042 3437
0.1333 -0.023
-0.454
3 -1 03/12/2008 0.043 3427
083 -0.987
-0.587
4 0 04/12/2008 0.477 3553 -0.546
0.456
0.623
5 +1 05/12/2008 0.567 3438 0.9865
0.387
0.218

I would like to add cells G3:G4 for every one of my stocks as I want to
calculate the AR for each. Each of the stocks has the same format, and
therefore, in-between the AR column for each of the stocks is exactly 7
columns.

Hopefully, I would like to put the new values into a new column, so I
can
just auto-fill down.

Thank you again. This is my first time posting here, I hope I have made
my
question clear enough.

Hi,

So you want to add up chunks of 6 cells in the same row staring from G4 and
all the way upto BCZ4. Therefore, you want to sum up G4:N4 + U4:AB4. If
this is indeed the case, you may use the following:

=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),14)={1;2;3;4;5;6;7;8})*(G4:BCZ4))

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

The cells I want to add up start from G4, then N4, U4... etc and the last
cell is BCZ4.

Actually, there is only 6 cells in between each cell I want to add up.

Thanks


"Ashish Mathur" wrote:

Hi,

OK, help me understand this better. If your data is in B3:U3, then what
do
you want to do:

1. Add B3, J3, R3 etc ;or
2. B3,C3,J3,K3,R3,S3

Please clarify

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

I think the alignment of the example made it quite confusing. I need to
add
up cells in the same row which are spaced 7 cells apart exactly.

But thank you for your help. If you could state a general formula, then
I
can try to fit in my own data.

Thank you.



"Ashish Mathur" wrote:

Hi,

This formula will sum up the two adjacent cells every 7 columns apart

=SUMPRODUCT((MOD((COLUMN(B3:U3)-COLUMN($A$3)),9)={1;2})*(B3:U3))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hello,

Thank you for helping, as I am not too familiar with the advanced
funtions
of Excel.

I am working with a very large worksheet and would like to add cells
together. I would like to add two cells (on two adjacent rows but
same
column) every 7 columns apart.


eg.
A B C D E
F G
1 Days Date Value XAO Return
XAO
Return AR
2 -2 02/12/2008 0.042 3437
0.1333 -0.023
-0.454
3 -1 03/12/2008 0.043 3427
083 -0.987
-0.587
4 0 04/12/2008 0.477 3553 -0.546
0.456
0.623
5 +1 05/12/2008 0.567 3438 0.9865
0.387
0.218

I would like to add cells G3:G4 for every one of my stocks as I want
to
calculate the AR for each. Each of the stocks has the same format,
and
therefore, in-between the AR column for each of the stocks is
exactly 7
columns.

Hopefully, I would like to put the new values into a new column, so
I
can
just auto-fill down.

Thank you again. This is my first time posting here, I hope I have
made
my
question clear enough.

Hi,

Sorry. I haven't made myself that clear again. It is just single cells that
I want to add up, the first is at G4, the next is N4, then U4 etc etc...
until the last cell is BCZ4. So, every interval space of 6 cells. The values
are all in the same row.

Your other comments have been helpful to me to learn more about Excel. I
didn't know Excel was this powerful =P until I started to use it properly. I
always thought it could only sum or subtract numbers together.

"Ashish Mathur" wrote:

Hi,

So you want to add up chunks of 6 cells in the same row staring from G4 and
all the way upto BCZ4. Therefore, you want to sum up G4:N4 + U4:AB4. If
this is indeed the case, you may use the following:

=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),14)={1;2;3;4;5;6;7;8})*(G4:BCZ4))

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

The cells I want to add up start from G4, then N4, U4... etc and the last
cell is BCZ4.

Actually, there is only 6 cells in between each cell I want to add up.

Thanks


"Ashish Mathur" wrote:

Hi,

OK, help me understand this better. If your data is in B3:U3, then what
do
you want to do:

1. Add B3, J3, R3 etc ;or
2. B3,C3,J3,K3,R3,S3

Please clarify

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

I think the alignment of the example made it quite confusing. I need to
add
up cells in the same row which are spaced 7 cells apart exactly.

But thank you for your help. If you could state a general formula, then
I
can try to fit in my own data.

Thank you.



"Ashish Mathur" wrote:

Hi,

This formula will sum up the two adjacent cells every 7 columns apart

=SUMPRODUCT((MOD((COLUMN(B3:U3)-COLUMN($A$3)),9)={1;2})*(B3:U3))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hello,

Thank you for helping, as I am not too familiar with the advanced
funtions
of Excel.

I am working with a very large worksheet and would like to add cells
together. I would like to add two cells (on two adjacent rows but
same
column) every 7 columns apart.


eg.
A B C D E
F G
1 Days Date Value XAO Return
XAO
Return AR
2 -2 02/12/2008 0.042 3437
0.1333 -0.023
-0.454
3 -1 03/12/2008 0.043 3427
083 -0.987
-0.587
4 0 04/12/2008 0.477 3553 -0.546
0.456
0.623
5 +1 05/12/2008 0.567 3438 0.9865
0.387
0.218

I would like to add cells G3:G4 for every one of my stocks as I want
to
calculate the AR for each. Each of the stocks has the same format,
and
therefore, in-between the AR column for each of the stocks is
exactly 7
columns.

Hopefully, I would like to put the new values into a new column, so
I
can
just auto-fill down.

Thank you again. This is my first time posting here, I hope I have
made
my
question clear enough.

Hi,

Try this

=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),7)=1)*(G4:BCZ4))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

Sorry. I haven't made myself that clear again. It is just single cells
that
I want to add up, the first is at G4, the next is N4, then U4 etc etc...
until the last cell is BCZ4. So, every interval space of 6 cells. The
values
are all in the same row.

Your other comments have been helpful to me to learn more about Excel. I
didn't know Excel was this powerful =P until I started to use it properly.
I
always thought it could only sum or subtract numbers together.

"Ashish Mathur" wrote:

Hi,

So you want to add up chunks of 6 cells in the same row staring from G4
and
all the way upto BCZ4. Therefore, you want to sum up G4:N4 + U4:AB4. If
this is indeed the case, you may use the following:

=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),14)={1;2;3;4;5;6;7;8})*(G4:BCZ4))

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

The cells I want to add up start from G4, then N4, U4... etc and the
last
cell is BCZ4.

Actually, there is only 6 cells in between each cell I want to add up.

Thanks


"Ashish Mathur" wrote:

Hi,

OK, help me understand this better. If your data is in B3:U3, then
what
do
you want to do:

1. Add B3, J3, R3 etc ;or
2. B3,C3,J3,K3,R3,S3

Please clarify

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

I think the alignment of the example made it quite confusing. I need
to
add
up cells in the same row which are spaced 7 cells apart exactly.

But thank you for your help. If you could state a general formula,
then
I
can try to fit in my own data.

Thank you.



"Ashish Mathur" wrote:

Hi,

This formula will sum up the two adjacent cells every 7 columns
apart

=SUMPRODUCT((MOD((COLUMN(B3:U3)-COLUMN($A$3)),9)={1;2})*(B3:U3))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hello,

Thank you for helping, as I am not too familiar with the advanced
funtions
of Excel.

I am working with a very large worksheet and would like to add
cells
together. I would like to add two cells (on two adjacent rows but
same
column) every 7 columns apart.


eg.
A B C D
E
F G
1 Days Date Value XAO Return
XAO
Return AR
2 -2 02/12/2008 0.042 3437
0.1333 -0.023
-0.454
3 -1 03/12/2008 0.043 3427
083 -0.987
-0.587
4 0 04/12/2008 0.477 3553 -0.546
0.456
0.623
5 +1 05/12/2008 0.567 3438 0.9865
0.387
0.218

I would like to add cells G3:G4 for every one of my stocks as I
want
to
calculate the AR for each. Each of the stocks has the same
format,
and
therefore, in-between the AR column for each of the stocks is
exactly 7
columns.

Hopefully, I would like to put the new values into a new column,
so
I
can
just auto-fill down.

Thank you again. This is my first time posting here, I hope I
have
made
my
question clear enough.

Thank you so so much. Works wonderfully. It is exactly the same as the values
I got by manually selecting 200+ cells using the SUM function.

"Ashish Mathur" wrote:

Hi,

Try this

=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),7)=1)*(G4:BCZ4))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

Sorry. I haven't made myself that clear again. It is just single cells
that
I want to add up, the first is at G4, the next is N4, then U4 etc etc...
until the last cell is BCZ4. So, every interval space of 6 cells. The
values
are all in the same row.

Your other comments have been helpful to me to learn more about Excel. I
didn't know Excel was this powerful =P until I started to use it properly.
I
always thought it could only sum or subtract numbers together.

"Ashish Mathur" wrote:

Hi,

So you want to add up chunks of 6 cells in the same row staring from G4
and
all the way upto BCZ4. Therefore, you want to sum up G4:N4 + U4:AB4. If
this is indeed the case, you may use the following:

=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),14)={1;2;3;4;5;6;7;8})*(G4:BCZ4))

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

The cells I want to add up start from G4, then N4, U4... etc and the
last
cell is BCZ4.

Actually, there is only 6 cells in between each cell I want to add up.

Thanks


"Ashish Mathur" wrote:

Hi,

OK, help me understand this better. If your data is in B3:U3, then
what
do
you want to do:

1. Add B3, J3, R3 etc ;or
2. B3,C3,J3,K3,R3,S3

Please clarify

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

I think the alignment of the example made it quite confusing. I need
to
add
up cells in the same row which are spaced 7 cells apart exactly.

But thank you for your help. If you could state a general formula,
then
I
can try to fit in my own data.

Thank you.



"Ashish Mathur" wrote:

Hi,

This formula will sum up the two adjacent cells every 7 columns
apart

=SUMPRODUCT((MOD((COLUMN(B3:U3)-COLUMN($A$3)),9)={1;2})*(B3:U3))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hello,

Thank you for helping, as I am not too familiar with the advanced
funtions
of Excel.

I am working with a very large worksheet and would like to add
cells
together. I would like to add two cells (on two adjacent rows but
same
column) every 7 columns apart.


eg.
A B C D
E
F G
1 Days Date Value XAO Return
XAO
Return AR
2 -2 02/12/2008 0.042 3437
0.1333 -0.023
-0.454
3 -1 03/12/2008 0.043 3427
083 -0.987
-0.587
4 0 04/12/2008 0.477 3553 -0.546
0.456
0.623
5 +1 05/12/2008 0.567 3438 0.9865
0.387
0.218

I would like to add cells G3:G4 for every one of my stocks as I
want
to
calculate the AR for each. Each of the stocks has the same
format,
and
therefore, in-between the AR column for each of the stocks is
exactly 7
columns.

Hopefully, I would like to put the new values into a new column,
so
I
can
just auto-fill down.

Thank you again. This is my first time posting here, I hope I
have
made
my
question clear enough.

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Thank you so so much. Works wonderfully. It is exactly the same as the
values
I got by manually selecting 200+ cells using the SUM function.

"Ashish Mathur" wrote:

Hi,

Try this

=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),7)=1)*(G4:BCZ4))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

Sorry. I haven't made myself that clear again. It is just single cells
that
I want to add up, the first is at G4, the next is N4, then U4 etc
etc...
until the last cell is BCZ4. So, every interval space of 6 cells. The
values
are all in the same row.

Your other comments have been helpful to me to learn more about Excel.
I
didn't know Excel was this powerful =P until I started to use it
properly.
I
always thought it could only sum or subtract numbers together.

"Ashish Mathur" wrote:

Hi,

So you want to add up chunks of 6 cells in the same row staring from
G4
and
all the way upto BCZ4. Therefore, you want to sum up G4:N4 + U4:AB4.
If
this is indeed the case, you may use the following:

=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),14)={1;2;3;4;5;6;7;8})*(G4:BCZ4))

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

The cells I want to add up start from G4, then N4, U4... etc and the
last
cell is BCZ4.

Actually, there is only 6 cells in between each cell I want to add
up.

Thanks


"Ashish Mathur" wrote:

Hi,

OK, help me understand this better. If your data is in B3:U3, then
what
do
you want to do:

1. Add B3, J3, R3 etc ;or
2. B3,C3,J3,K3,R3,S3

Please clarify

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hi,

I think the alignment of the example made it quite confusing. I
need
to
add
up cells in the same row which are spaced 7 cells apart exactly.

But thank you for your help. If you could state a general
formula,
then
I
can try to fit in my own data.

Thank you.



"Ashish Mathur" wrote:

Hi,

This formula will sum up the two adjacent cells every 7 columns
apart

=SUMPRODUCT((MOD((COLUMN(B3:U3)-COLUMN($A$3)),9)={1;2})*(B3:U3))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Eve" wrote in message
...
Hello,

Thank you for helping, as I am not too familiar with the
advanced
funtions
of Excel.

I am working with a very large worksheet and would like to add
cells
together. I would like to add two cells (on two adjacent rows
but
same
column) every 7 columns apart.


eg.
A B C D
E
F G
1 Days Date Value XAO Return
XAO
Return AR
2 -2 02/12/2008 0.042 3437
0.1333 -0.023
-0.454
3 -1 03/12/2008 0.043 3427
083 -0.987
-0.587
4 0 04/12/2008 0.477 3553 -0.546
0.456
0.623
5 +1 05/12/2008 0.567 3438 0.9865
0.387
0.218

I would like to add cells G3:G4 for every one of my stocks as
I
want
to
calculate the AR for each. Each of the stocks has the same
format,
and
therefore, in-between the AR column for each of the stocks is
exactly 7
columns.

Hopefully, I would like to put the new values into a new
column,
so
I
can
just auto-fill down.

Thank you again. This is my first time posting here, I hope I
have
made
my
question clear enough.