| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
| How to limit columns that display based on selection of a dropdown value | Excel Worksheet Functions | |||
| Columns are missing when printing a selection | Excel Discussion (Misc queries) | |||
| auto fill columns based on a selection | Excel Discussion (Misc queries) | |||
| Selecting Multiple Columns in a Named Selection | Excel Worksheet Functions | |||
Linear Mode
