Selection of Columns for SUM
 

I have a table for sales in each month of the year (have used only 6 mths is
example) and want to be able to calculate the total sales for any given
period. The given period.The "from" and "to" will be sourced from two other
cells, I have put these cells in " " in my example.
eg
Period from "A"
Period to "N"
Table as below
Period J A S O N D
Sales 1 2 4 1 3 6 Answer = 10 (2+4+1+3)
Sales 2 3 4 1 4 3 Answer = 12 (3+4+1+4)

I have thied the IF fuction but gets complicated with the limitation of 7
variables as my real table is 12 months.

Selection of Columns for SUM
 

Ross

No doubt an answer will be forthcoming but what do you anticipate happening
forJanuary (J), June (J) July(J) and several others?

Mike


"Ross OZ" wrote:

I have a table for sales in each month of the year (have used only 6 mths is
example) and want to be able to calculate the total sales for any given
period. The given period.The "from" and "to" will be sourced from two other
cells, I have put these cells in " " in my example.
eg
Period from "A"
Period to "N"
Table as below
Period J A S O N D
Sales 1 2 4 1 3 6 Answer = 10 (2+4+1+3)
Sales 2 3 4 1 4 3 Answer = 12 (3+4+1+4)

I have thied the IF fuction but gets complicated with the limitation of 7
variables as my real table is 12 months.

Selection of Columns for SUM
 

Mike
Don't know what you mean.
If you are saying that June, January and July start with J as May and March
start with M, it is only the example I used - forget the columns - call them
A, B, C, D, E, F, G etc
Then I want to be able to add all the cells from B to E and then C to F
simply by changing the letters in two cells outside the table. The
calculation will always include all the columns between two nominated columns
ie
the B to E selection will be the SUM of columns B, C, D, and E
the C to F selection will be the SUM of columns C, D, and F

"Mike H" wrote:

Ross

No doubt an answer will be forthcoming but what do you anticipate happening
forJanuary (J), June (J) July(J) and several others?

Mike


"Ross OZ" wrote:

I have a table for sales in each month of the year (have used only 6 mths is
example) and want to be able to calculate the total sales for any given
period. The given period.The "from" and "to" will be sourced from two other
cells, I have put these cells in " " in my example.
eg
Period from "A"
Period to "N"
Table as below
Period J A S O N D
Sales 1 2 4 1 3 6 Answer = 10 (2+4+1+3)
Sales 2 3 4 1 4 3 Answer = 12 (3+4+1+4)

I have thied the IF fuction but gets complicated with the limitation of 7
variables as my real table is 12 months.

Selection of Columns for SUM
 

Last line should read
the C to F selection will be the SUM of columns C, D, and F
and should have read
the C to F selection will be the SUM of columns C, D, E and F
Didn't want to confuse further!


"Ross OZ" wrote:

Mike
Don't know what you mean.
If you are saying that June, January and July start with J as May and March
start with M, it is only the example I used - forget the columns - call them
A, B, C, D, E, F, G etc
Then I want to be able to add all the cells from B to E and then C to F
simply by changing the letters in two cells outside the table. The
calculation will always include all the columns between two nominated columns
ie
the B to E selection will be the SUM of columns B, C, D, and E
the C to F selection will be the SUM of columns C, D, and F

"Mike H" wrote:

Ross

No doubt an answer will be forthcoming but what do you anticipate happening
forJanuary (J), June (J) July(J) and several others?

Mike


"Ross OZ" wrote:

I have a table for sales in each month of the year (have used only 6 mths is
example) and want to be able to calculate the total sales for any given
period. The given period.The "from" and "to" will be sourced from two other
cells, I have put these cells in " " in my example.
eg
Period from "A"
Period to "N"
Table as below
Period J A S O N D
Sales 1 2 4 1 3 6 Answer = 10 (2+4+1+3)
Sales 2 3 4 1 4 3 Answer = 12 (3+4+1+4)

I have thied the IF fuction but gets complicated with the limitation of 7
variables as my real table is 12 months.

Selection of Columns for SUM
 

Probably not the answer that you are looking for but, label your Columns in
Row 1: Jul, Aug, Sep, etc, instead of J, A, S, so that you don't get
duplicates, (which I think Mike was trying to point out to you). My table
is in A1:G3.

Then in two cells outside the table enter Aug and Nov respectively, I used
J5 & K5.

then use the formula:

=SUM(INDEX(A2:G2,MATCH(J5,A1:G1,0)):INDEX(A2:G2,MA TCH(K5,A1:G1,0)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Ross OZ" wrote in message
...
Mike
Don't know what you mean.
If you are saying that June, January and July start with J as May and
March
start with M, it is only the example I used - forget the columns - call
them
A, B, C, D, E, F, G etc
Then I want to be able to add all the cells from B to E and then C to F
simply by changing the letters in two cells outside the table. The
calculation will always include all the columns between two nominated
columns
ie
the B to E selection will be the SUM of columns B, C, D, and E
the C to F selection will be the SUM of columns C, D, and F

"Mike H" wrote:

Ross

No doubt an answer will be forthcoming but what do you anticipate
happening
forJanuary (J), June (J) July(J) and several others?

Mike


"Ross OZ" wrote:

I have a table for sales in each month of the year (have used only 6
mths is
example) and want to be able to calculate the total sales for any given
period. The given period.The "from" and "to" will be sourced from two
other
cells, I have put these cells in " " in my example.
eg
Period from "A"
Period to "N"
Table as below
Period J A S O N D
Sales 1 2 4 1 3 6 Answer = 10 (2+4+1+3)
Sales 2 3 4 1 4 3 Answer = 12 (3+4+1+4)

I have thied the IF fuction but gets complicated with the limitation of
7
variables as my real table is 12 months.

Selection of Columns for SUM
 

Sandy
Thank you so much, you don't know how many hours this will save me.
Two things I say about Excel
(1) 11th wonder of the world
(2) You can learn something new every day.
Your example works perfectly and is exactly what I wanted!
Thanks - my shout when I am next in Scotland!


"Sandy Mann" wrote:

Probably not the answer that you are looking for but, label your Columns in
Row 1: Jul, Aug, Sep, etc, instead of J, A, S, so that you don't get
duplicates, (which I think Mike was trying to point out to you). My table
is in A1:G3.

Then in two cells outside the table enter Aug and Nov respectively, I used
J5 & K5.

then use the formula:

=SUM(INDEX(A2:G2,MATCH(J5,A1:G1,0)):INDEX(A2:G2,MA TCH(K5,A1:G1,0)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Ross OZ" wrote in message
...
Mike
Don't know what you mean.
If you are saying that June, January and July start with J as May and
March
start with M, it is only the example I used - forget the columns - call
them
A, B, C, D, E, F, G etc
Then I want to be able to add all the cells from B to E and then C to F
simply by changing the letters in two cells outside the table. The
calculation will always include all the columns between two nominated
columns
ie
the B to E selection will be the SUM of columns B, C, D, and E
the C to F selection will be the SUM of columns C, D, and F

"Mike H" wrote:

Ross

No doubt an answer will be forthcoming but what do you anticipate
happening
forJanuary (J), June (J) July(J) and several others?

Mike


"Ross OZ" wrote:

I have a table for sales in each month of the year (have used only 6
mths is
example) and want to be able to calculate the total sales for any given
period. The given period.The "from" and "to" will be sourced from two
other
cells, I have put these cells in " " in my example.
eg
Period from "A"
Period to "N"
Table as below
Period J A S O N D
Sales 1 2 4 1 3 6 Answer = 10 (2+4+1+3)
Sales 2 3 4 1 4 3 Answer = 12 (3+4+1+4)

I have thied the IF fuction but gets complicated with the limitation of
7
variables as my real table is 12 months.

Selection of Columns for SUM
 

You're vary welcome. Thanks for the feedback.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Ross OZ" wrote in message
...
Sandy
Thank you so much, you don't know how many hours this will save me.
Two things I say about Excel
(1) 11th wonder of the world
(2) You can learn something new every day.
Your example works perfectly and is exactly what I wanted!
Thanks - my shout when I am next in Scotland!


"Sandy Mann" wrote:

Probably not the answer that you are looking for but, label your Columns
in
Row 1: Jul, Aug, Sep, etc, instead of J, A, S, so that you don't get
duplicates, (which I think Mike was trying to point out to you). My
table
is in A1:G3.

Then in two cells outside the table enter Aug and Nov respectively, I
used
J5 & K5.

then use the formula:

=SUM(INDEX(A2:G2,MATCH(J5,A1:G1,0)):INDEX(A2:G2,MA TCH(K5,A1:G1,0)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Ross OZ" wrote in message
...
Mike
Don't know what you mean.
If you are saying that June, January and July start with J as May and
March
start with M, it is only the example I used - forget the columns - call
them
A, B, C, D, E, F, G etc
Then I want to be able to add all the cells from B to E and then C to F
simply by changing the letters in two cells outside the table. The
calculation will always include all the columns between two nominated
columns
ie
the B to E selection will be the SUM of columns B, C, D, and E
the C to F selection will be the SUM of columns C, D, and F

"Mike H" wrote:

Ross

No doubt an answer will be forthcoming but what do you anticipate
happening
forJanuary (J), June (J) July(J) and several others?

Mike


"Ross OZ" wrote:

I have a table for sales in each month of the year (have used only 6
mths is
example) and want to be able to calculate the total sales for any
given
period. The given period.The "from" and "to" will be sourced from
two
other
cells, I have put these cells in " " in my example.
eg
Period from "A"
Period to "N"
Table as below
Period J A S O N D
Sales 1 2 4 1 3 6 Answer = 10 (2+4+1+3)
Sales 2 3 4 1 4 3 Answer = 12 (3+4+1+4)

I have thied the IF fuction but gets complicated with the limitation
of
7
variables as my real table is 12 months.