| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional/variable sum??
Hi, I am trying to sum the sales values of months (say Jan to Mar) with the
start month being fixed and the end month being a variable. I have therefore named a cell as my EndMonth and want my sum to change automatically every time I change my EndMonth variable. ie. if the sales values for Jan to Dec are in range B2 to M2, I want my sum to change automatically from B2:D2 for the period Jan to Mar to B2:G2 for the period Jan to Jun. Is there a function for this? I tried Index for no luck. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional/variable sum??
A B C D E F
G EndMonth jan feb mar apr may etc 4 10 25 3 44 5 Put the number of the month in A2 (for april 4) the formula =SUM(B2:CHOOSE(A2-1,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2)) is sumarizes for you each row for the range of B2:to the endMonth Click yes if helped -- Greatly appreciated Eva "Costas Limassol" wrote: Hi, I am trying to sum the sales values of months (say Jan to Mar) with the start month being fixed and the end month being a variable. I have therefore named a cell as my EndMonth and want my sum to change automatically every time I change my EndMonth variable. ie. if the sales values for Jan to Dec are in range B2 to M2, I want my sum to change automatically from B2:D2 for the period Jan to Mar to B2:G2 for the period Jan to Jun. Is there a function for this? I tried Index for no luck. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional/variable sum??
I have a hard time understanding how your data looks like, so can you show
the exmaple? -- Greatly appreciated Eva "Costas Limassol" wrote: Hi Eva, Thanks for the advise. It worked but I tried to take it a step further so I can look up the name or account number of the income/expense from a range and then sum the months. Here is the formula that I've used: =sum(VLOOKUP($A7;$A$6:$AD$12;18;FALSE):CHOOSE(End_ Period;VLOOKUP($A7;$A$6:$AD$12;18;FALSE);VLOOKUP($ A7;$A$6:$AD$12;19;FALSE);VLOOKUP($A7;$A$6:$AD$12;2 0;FALSE);VLOOKUP($A7;$A$6:$AD$12;21;FALSE);VLOOKUP ($A7;$A$6:$AD$12;22;FALSE);VLOOKUP($A7;$A$6:$AD$12 ;23;FALSE);VLOOKUP($A7;$A$6:$AD$12;24;FALSE);VLOOK UP($A7;$A$6:$AD$12;25;FALSE);VLOOKUP($A7;$A$6:$AD$ 12;26;FALSE);VLOOKUP($A7;$A$6:$AD$12;27;FALSE);VLO OKUP($A7;$A$6:$AD$12;28;FALSE);VLOOKUP($A7;$A$6:$A D$12;29;FALSE))) I know it's a very complex formula but it seems logical so I can't understand why it will not accept it saying that the formula contains an error!! "Eva" wrote: A B C D E F G EndMonth jan feb mar apr may etc 4 10 25 3 44 5 Put the number of the month in A2 (for april 4) the formula =SUM(B2:CHOOSE(A2-1,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2)) is sumarizes for you each row for the range of B2:to the endMonth Click yes if helped -- Greatly appreciated Eva "Costas Limassol" wrote: Hi, I am trying to sum the sales values of months (say Jan to Mar) with the start month being fixed and the end month being a variable. I have therefore named a cell as my EndMonth and want my sum to change automatically every time I change my EndMonth variable. ie. if the sales values for Jan to Dec are in range B2 to M2, I want my sum to change automatically from B2:D2 for the period Jan to Mar to B2:G2 for the period Jan to Jun. Is there a function for this? I tried Index for no luck. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional/variable sum??
Hi Eva,
Thanks for the advise. It worked but I tried to take it a step further so I can look up the name or account number of the income/expense from a range and then sum the months. Here is the formula that I've used: =sum(VLOOKUP($A7;$A$6:$AD$12;18;FALSE):CHOOSE(End_ Period;VLOOKUP($A7;$A$6:$AD$12;18;FALSE);VLOOKUP($ A7;$A$6:$AD$12;19;FALSE);VLOOKUP($A7;$A$6:$AD$12;2 0;FALSE);VLOOKUP($A7;$A$6:$AD$12;21;FALSE);VLOOKUP ($A7;$A$6:$AD$12;22;FALSE);VLOOKUP($A7;$A$6:$AD$12 ;23;FALSE);VLOOKUP($A7;$A$6:$AD$12;24;FALSE);VLOOK UP($A7;$A$6:$AD$12;25;FALSE);VLOOKUP($A7;$A$6:$AD$ 12;26;FALSE);VLOOKUP($A7;$A$6:$AD$12;27;FALSE);VLO OKUP($A7;$A$6:$AD$12;28;FALSE);VLOOKUP($A7;$A$6:$A D$12;29;FALSE))) I know it's a very complex formula but it seems logical so I can't understand why it will not accept it saying that the formula contains an error!! "Eva" wrote: A B C D E F G EndMonth jan feb mar apr may etc 4 10 25 3 44 5 Put the number of the month in A2 (for april 4) the formula =SUM(B2:CHOOSE(A2-1,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2)) is sumarizes for you each row for the range of B2:to the endMonth Click yes if helped -- Greatly appreciated Eva "Costas Limassol" wrote: Hi, I am trying to sum the sales values of months (say Jan to Mar) with the start month being fixed and the end month being a variable. I have therefore named a cell as my EndMonth and want my sum to change automatically every time I change my EndMonth variable. ie. if the sales values for Jan to Dec are in range B2 to M2, I want my sum to change automatically from B2:D2 for the period Jan to Mar to B2:G2 for the period Jan to Jun. Is there a function for this? I tried Index for no luck. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional/variable sum??
Hi Eva,
This is an example of my data: 1 A B C D E F G H I J K L M N 2 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total 3 ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ 4 5 Sales 29.871 28.666 28.400 27.936 28.316 27.936 27.586 38.834 28.486 28.936 28.316 27.936 351.220 6 C.O.S. 7 G. P. 8 9 Payroll etc until line 50. I have changed the references to my formula so it matches the example. There's also a named area called "Data" with ref A5:N50. =sum(VLOOKUP($A5;Data;2;FALSE):CHOOSE(End_Period;V LOOKUP($A5;Data;2;FALSE);VLOOKUP($A5;Data;3;FALSE) ;VLOOKUP($A5;Data;4;FALSE); VLOOKUP($A5;Data;5;FALSE);VLOOKUP($A5;Data;6;FALSE );VLOOKUP($A5;Data;7;FALSE);VLOOKUP($A5;Data;8;FAL SE);VLOOKUP($A5;Data;9;FALSE); VLOOKUP($A5;Data;10;FALSE);VLOOKUP($A5;Data;11;FAL SE);VLOOKUP($A5;Data;12;FALSE);VLOOKUP($A5;Data;13 ;FALSE))) I hope this is clearer. Thanks Costas "Eva" wrote: I have a hard time understanding how your data looks like, so can you show the exmaple? -- Greatly appreciated Eva "Costas Limassol" wrote: Hi Eva, Thanks for the advise. It worked but I tried to take it a step further so I can look up the name or account number of the income/expense from a range and then sum the months. Here is the formula that I've used: =sum(VLOOKUP($A7;$A$6:$AD$12;18;FALSE):CHOOSE(End_ Period;VLOOKUP($A7;$A$6:$AD$12;18;FALSE);VLOOKUP($ A7;$A$6:$AD$12;19;FALSE);VLOOKUP($A7;$A$6:$AD$12;2 0;FALSE);VLOOKUP($A7;$A$6:$AD$12;21;FALSE);VLOOKUP ($A7;$A$6:$AD$12;22;FALSE);VLOOKUP($A7;$A$6:$AD$12 ;23;FALSE);VLOOKUP($A7;$A$6:$AD$12;24;FALSE);VLOOK UP($A7;$A$6:$AD$12;25;FALSE);VLOOKUP($A7;$A$6:$AD$ 12;26;FALSE);VLOOKUP($A7;$A$6:$AD$12;27;FALSE);VLO OKUP($A7;$A$6:$AD$12;28;FALSE);VLOOKUP($A7;$A$6:$A D$12;29;FALSE))) I know it's a very complex formula but it seems logical so I can't understand why it will not accept it saying that the formula contains an error!! "Eva" wrote: A B C D E F G EndMonth jan feb mar apr may etc 4 10 25 3 44 5 Put the number of the month in A2 (for april 4) the formula =SUM(B2:CHOOSE(A2-1,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2)) is sumarizes for you each row for the range of B2:to the endMonth Click yes if helped -- Greatly appreciated Eva "Costas Limassol" wrote: Hi, I am trying to sum the sales values of months (say Jan to Mar) with the start month being fixed and the end month being a variable. I have therefore named a cell as my EndMonth and want my sum to change automatically every time I change my EndMonth variable. ie. if the sales values for Jan to Dec are in range B2 to M2, I want my sum to change automatically from B2:D2 for the period Jan to Mar to B2:G2 for the period Jan to Jun. Is there a function for this? I tried Index for no luck. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional/variable sum??
Hi
I tried your example, and it looks like vlookup can't work with choose, but why you need it anyway? you are doing it by line by line, so mayby you can use my first formula to sum your data by row. Click yes if helped -- Greatly appreciated Eva "Costas Limassol" wrote: Hi Eva, This is an example of my data: 1 A B C D E F G H I J K L M N 2 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total 3 ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ ぎ 4 5 Sales 29.871 28.666 28.400 27.936 28.316 27.936 27.586 38.834 28.486 28.936 28.316 27.936 351.220 6 C.O.S. 7 G. P. 8 9 Payroll etc until line 50. I have changed the references to my formula so it matches the example. There's also a named area called "Data" with ref A5:N50. =sum(VLOOKUP($A5;Data;2;FALSE):CHOOSE(End_Period;V LOOKUP($A5;Data;2;FALSE);VLOOKUP($A5;Data;3;FALSE) ;VLOOKUP($A5;Data;4;FALSE); VLOOKUP($A5;Data;5;FALSE);VLOOKUP($A5;Data;6;FALSE );VLOOKUP($A5;Data;7;FALSE);VLOOKUP($A5;Data;8;FAL SE);VLOOKUP($A5;Data;9;FALSE); VLOOKUP($A5;Data;10;FALSE);VLOOKUP($A5;Data;11;FAL SE);VLOOKUP($A5;Data;12;FALSE);VLOOKUP($A5;Data;13 ;FALSE))) I hope this is clearer. Thanks Costas "Eva" wrote: I have a hard time understanding how your data looks like, so can you show the exmaple? -- Greatly appreciated Eva "Costas Limassol" wrote: Hi Eva, Thanks for the advise. It worked but I tried to take it a step further so I can look up the name or account number of the income/expense from a range and then sum the months. Here is the formula that I've used: =sum(VLOOKUP($A7;$A$6:$AD$12;18;FALSE):CHOOSE(End_ Period;VLOOKUP($A7;$A$6:$AD$12;18;FALSE);VLOOKUP($ A7;$A$6:$AD$12;19;FALSE);VLOOKUP($A7;$A$6:$AD$12;2 0;FALSE);VLOOKUP($A7;$A$6:$AD$12;21;FALSE);VLOOKUP ($A7;$A$6:$AD$12;22;FALSE);VLOOKUP($A7;$A$6:$AD$12 ;23;FALSE);VLOOKUP($A7;$A$6:$AD$12;24;FALSE);VLOOK UP($A7;$A$6:$AD$12;25;FALSE);VLOOKUP($A7;$A$6:$AD$ 12;26;FALSE);VLOOKUP($A7;$A$6:$AD$12;27;FALSE);VLO OKUP($A7;$A$6:$AD$12;28;FALSE);VLOOKUP($A7;$A$6:$A D$12;29;FALSE))) I know it's a very complex formula but it seems logical so I can't understand why it will not accept it saying that the formula contains an error!! "Eva" wrote: A B C D E F G EndMonth jan feb mar apr may etc 4 10 25 3 44 5 Put the number of the month in A2 (for april 4) the formula =SUM(B2:CHOOSE(A2-1,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2)) is sumarizes for you each row for the range of B2:to the endMonth Click yes if helped -- Greatly appreciated Eva "Costas Limassol" wrote: Hi, I am trying to sum the sales values of months (say Jan to Mar) with the start month being fixed and the end month being a variable. I have therefore named a cell as my EndMonth and want my sum to change automatically every time I change my EndMonth variable. ie. if the sales values for Jan to Dec are in range B2 to M2, I want my sum to change automatically from B2:D2 for the period Jan to Mar to B2:G2 for the period Jan to Jun. Is there a function for this? I tried Index for no luck. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional/variable sum??
Hi, I am trying to sum the sales values of months (say Jan to Mar) with the
start month being fixed and the end month being a variable. ... If the month number is in A2 (4 for April or 5 for May, etc.) then the sum is =SUM(OFFSET(B2,0,0,1,A2)) If instead the month name is in A2, then the sum is =SUM(OFFSET(B2,0,0,1,MONTH(DATEVALUE(A2&" 1, 2000")))) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional/variable sum??
Hi,
try this =sum(B2:index(b1:M2,2,match(B4,B1:M1,0))) B1:M1 has Jan, Feb etc. In cell B4, enter one of the months mentioned in range B1:M1 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Costas Limassol" wrote in message ... Hi, I am trying to sum the sales values of months (say Jan to Mar) with the start month being fixed and the end month being a variable. I have therefore named a cell as my EndMonth and want my sum to change automatically every time I change my EndMonth variable. ie. if the sales values for Jan to Dec are in range B2 to M2, I want my sum to change automatically from B2:D2 for the period Jan to Mar to B2:G2 for the period Jan to Jun. Is there a function for this? I tried Index for no luck. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Sum - variable criteria | Excel Discussion (Misc queries) | |||
| Conditional Formatting using variable text | Excel Discussion (Misc queries) | |||
| variable height variable width stacked bar charts | Charts and Charting in Excel | |||
| Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
| why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
![Costas Limassol[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
