Selection of Columns for SUM

[Ross OZ]

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.

 

[Mike H]

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]

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

 

[Ross OZ]

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!

 

[Sandy Mann]

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,MATCH(K5,A1:G1,0)))

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ross OZ]

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]

You're vary welcome. Thanks for the feedback.

--

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk