| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Include YTD calculations?
Hello,
I have built a table with formulas similar to this. I need assistance on how I can create a YTD table based on what Period I select. =SUMPRODUCT(('2010 AOPII with Benefits'!$A$1:$A$248=F$27)*('2010 AOPII with Benefits'!$E$1:$Q$1=$C$3)*('2010 AOPII with Benefits'!$B$1:$B$248=$E28),'2010 AOPII with Benefits'!$E$1:$Q$248)-SUMPRODUCT(('2010 Actuals'!$A$1:$A$302=F$27)*('2010 Actuals'!$E$1:$Q$1=$C$3)*('2010 Actuals'!$B$1:$B$302=$E28),'2010 Actuals'!$E$1:$Q$302) NOTE: C3 is the Period that I choose from drop down selection. Any help is appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Include YTD calculations?
Hello Don,
I will do my best to explain. Can't send via email as it contains confidential information. B / (W) P5 CATEGORY NEDC SEDC SCDC SWDC RENT - BUILDING -4,329.58 34.50 1,340.00 6,703.81 RENT - EQUIPMENT 375.20 5,071.79 1,802.42 352.82 COMPUTER EXP / DATA COM -104.00 6.00 -105.00 138.00 UTILITIES 2,371.20 -379.00 -7,011.22 3,213.88 Here is a portion of a table that I use. Under the numbers is from two other tables (AOP & Actuals). I use sumproduct to look for each category (Rent) AND for each location (NEDC) AND what Period (P5), this is a drop down for all periods. This is working just fine for me. Now, I would like to use this format to encompass YTD numbers. The layout of AOP & Actuals tables are similar to it, with the exception that the columns are by Period. If I select P5, I would like to see numbers from P1-P5. The formula below is what is behind the numbers I used as stated above. Hope this helps. If not, let me know and I will try another method. Thanks again. "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "MrRJ" wrote in message ... Hello, I have built a table with formulas similar to this. I need assistance on how I can create a YTD table based on what Period I select. =SUMPRODUCT(('2010 AOPII with Benefits'!$A$1:$A$248=F$27)*('2010 AOPII with Benefits'!$E$1:$Q$1=$C$3)*('2010 AOPII with Benefits'!$B$1:$B$248=$E28),'2010 AOPII with Benefits'!$E$1:$Q$248)-SUMPRODUCT(('2010 Actuals'!$A$1:$A$302=F$27)*('2010 Actuals'!$E$1:$Q$1=$C$3)*('2010 Actuals'!$B$1:$B$302=$E28),'2010 Actuals'!$E$1:$Q$302) NOTE: C3 is the Period that I choose from drop down selection. Any help is appreciated. . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Include YTD calculations?
I do not have time to re-create your file. If you can't provide dummy info
then I will be unable to assist. -- Don Guillett Microsoft MVP Excel SalesAid Software "MrRJ" wrote in message ... Hello Don, I will do my best to explain. Can't send via email as it contains confidential information. B / (W) P5 CATEGORY NEDC SEDC SCDC SWDC RENT - BUILDING -4,329.58 34.50 1,340.00 6,703.81 RENT - EQUIPMENT 375.20 5,071.79 1,802.42 352.82 COMPUTER EXP / DATA COM -104.00 6.00 -105.00 138.00 UTILITIES 2,371.20 -379.00 -7,011.22 3,213.88 Here is a portion of a table that I use. Under the numbers is from two other tables (AOP & Actuals). I use sumproduct to look for each category (Rent) AND for each location (NEDC) AND what Period (P5), this is a drop down for all periods. This is working just fine for me. Now, I would like to use this format to encompass YTD numbers. The layout of AOP & Actuals tables are similar to it, with the exception that the columns are by Period. If I select P5, I would like to see numbers from P1-P5. The formula below is what is behind the numbers I used as stated above. Hope this helps. If not, let me know and I will try another method. Thanks again. "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "MrRJ" wrote in message ... Hello, I have built a table with formulas similar to this. I need assistance on how I can create a YTD table based on what Period I select. =SUMPRODUCT(('2010 AOPII with Benefits'!$A$1:$A$248=F$27)*('2010 AOPII with Benefits'!$E$1:$Q$1=$C$3)*('2010 AOPII with Benefits'!$B$1:$B$248=$E28),'2010 AOPII with Benefits'!$E$1:$Q$248)-SUMPRODUCT(('2010 Actuals'!$A$1:$A$302=F$27)*('2010 Actuals'!$E$1:$Q$1=$C$3)*('2010 Actuals'!$B$1:$B$302=$E28),'2010 Actuals'!$E$1:$Q$302) NOTE: C3 is the Period that I choose from drop down selection. Any help is appreciated. . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Include YTD calculations?
Thanks Don. I wasn't asking to re-create the file. I was merely asking for
a formula that I can use in conjuction with my sumproduct to come up with YTD figures. I will try this on my own. "Don Guillett" wrote: I do not have time to re-create your file. If you can't provide dummy info then I will be unable to assist. -- Don Guillett Microsoft MVP Excel SalesAid Software "MrRJ" wrote in message ... Hello Don, I will do my best to explain. Can't send via email as it contains confidential information. B / (W) P5 CATEGORY NEDC SEDC SCDC SWDC RENT - BUILDING -4,329.58 34.50 1,340.00 6,703.81 RENT - EQUIPMENT 375.20 5,071.79 1,802.42 352.82 COMPUTER EXP / DATA COM -104.00 6.00 -105.00 138.00 UTILITIES 2,371.20 -379.00 -7,011.22 3,213.88 Here is a portion of a table that I use. Under the numbers is from two other tables (AOP & Actuals). I use sumproduct to look for each category (Rent) AND for each location (NEDC) AND what Period (P5), this is a drop down for all periods. This is working just fine for me. Now, I would like to use this format to encompass YTD numbers. The layout of AOP & Actuals tables are similar to it, with the exception that the columns are by Period. If I select P5, I would like to see numbers from P1-P5. The formula below is what is behind the numbers I used as stated above. Hope this helps. If not, let me know and I will try another method. Thanks again. "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "MrRJ" wrote in message ... Hello, I have built a table with formulas similar to this. I need assistance on how I can create a YTD table based on what Period I select. =SUMPRODUCT(('2010 AOPII with Benefits'!$A$1:$A$248=F$27)*('2010 AOPII with Benefits'!$E$1:$Q$1=$C$3)*('2010 AOPII with Benefits'!$B$1:$B$248=$E28),'2010 AOPII with Benefits'!$E$1:$Q$248)-SUMPRODUCT(('2010 Actuals'!$A$1:$A$302=F$27)*('2010 Actuals'!$E$1:$Q$1=$C$3)*('2010 Actuals'!$B$1:$B$302=$E28),'2010 Actuals'!$E$1:$Q$302) NOTE: C3 is the Period that I choose from drop down selection. Any help is appreciated. . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can SUMPRODUCT include an *? | Excel Discussion (Misc queries) | |||
| Include calculations in the lookup function | Excel Worksheet Functions | |||
| Using check boxes to include/exclude data in calculations | Excel Discussion (Misc queries) | |||
| Include Worksheet | Excel Worksheet Functions | |||
| Sum if formula to include value in col Q when no value in col P for that row | Excel Discussion (Misc queries) | |||
Hybrid Mode
