Selection of Columns for SUM

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ross OZ - 17 Mar 2008 13:13 GMT

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.

Reply to this Message

Mike H - 17 Mar 2008 13:33 GMT

Ross

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

Mike

> 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
[quoted text clipped - 10 lines]
> I have thied the IF fuction but gets complicated with the limitation of 7
> variables as my real table is 12 months.

Reply to this Message

Ross OZ - 17 Mar 2008 23:06 GMT

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
>
[quoted text clipped - 17 lines]
> > I have thied the IF fuction but gets complicated with the limitation of 7
> > variables as my real table is 12 months.

Reply to this Message

Ross OZ - 18 Mar 2008 00:11 GMT

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!

> Mike
> Don't know what you mean.
[quoted text clipped - 29 lines]
> > > I have thied the IF fuction but gets complicated with the limitation of 7
> > > variables as my real table is 12 months.

Reply to this Message

Sandy Mann - 18 Mar 2008 00:14 GMT

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)))

Signature

HTH

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

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

> Mike
> Don't know what you mean.
[quoted text clipped - 36 lines]
>> > 7
>> > variables as my real table is 12 months.

Reply to this Message

Ross OZ - 18 Mar 2008 00:52 GMT

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!

> 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
[quoted text clipped - 48 lines]
> >> > 7
> >> > variables as my real table is 12 months.

Reply to this Message

Sandy Mann - 18 Mar 2008 01:03 GMT

You're vary welcome. Thanks for the feedback.

Signature

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

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

> Sandy
> Thank you so much, you don't know how many hours this will save me.
[quoted text clipped - 62 lines]
>> >> > 7
>> >> > variables as my real table is 12 months.

Reply to this Message