Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to find out a solution for a complex formula
I've working with two different sheets in the same workbook. The first sheet is called "main flash" and the second is called "actuals". The "main flash sheet is basically just a cover page the has totals for a day, week, month, a year. The "actuals" sheet has all of the numbers. My "actuals" sheet looks like this: Code: -------------------- Column A Date Sales 3/11 10 3/12 15 3/13 10 3/14 20 3/15 30 3/16 20 3/17 30 Total 135 3/18 20 3/19 10 3/20 25 3/21 20 3/22 15 3/23 20 3/24 30 Total 140 -------------------- The Total's listed are for each week. In my "main flash" sheet I've got the date I want information from as well as the last day of that week. So for example today I have 3/22/06 listed in cell A1 and 3/24/06 listed in cell B1. What I'm hoping to do is have a cell in my "main flash" sheet display the total sales for the week from the start of the week up until the date listed in A1. So for example since I have 3/22/06 listed in the daily date and 3/24/06 listed in the weekly end date the formula would need to display the value of 3/18 (the start of the week) threw 3/22/06. So that would be 20+10+25+20+15 for a value of 90. Another example would be if I changed the daily date to 3/13/06 and the weekly end date to 3/17/06 the value would be 35 (10+15+10). If anyone can help me out with this I'd greatly appreciate it. -- Weasel ------------------------------------------------------------------------ Weasel's Profile: http://www.excelforum.com/member.php...o&userid=27206 View this thread: http://www.excelforum.com/showthread...hreadid=525297 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to find out a solution for a complex formula
In Main Flash, enter:
=SUMPRODUCT(--(actuals!A2:A16<=A1),--(actuals!A2:A16A2-7),actuals!B2:B16) The weekly totals in "actuals" are skipped because of the "Total" text. HTH -- AP "Weasel" a écrit dans le message de ... I've working with two different sheets in the same workbook. The first sheet is called "main flash" and the second is called "actuals". The "main flash sheet is basically just a cover page the has totals for a day, week, month, a year. The "actuals" sheet has all of the numbers. My "actuals" sheet looks like this: Code: -------------------- Column A Date Sales 3/11 10 3/12 15 3/13 10 3/14 20 3/15 30 3/16 20 3/17 30 Total 135 3/18 20 3/19 10 3/20 25 3/21 20 3/22 15 3/23 20 3/24 30 Total 140 -------------------- The Total's listed are for each week. In my "main flash" sheet I've got the date I want information from as well as the last day of that week. So for example today I have 3/22/06 listed in cell A1 and 3/24/06 listed in cell B1. What I'm hoping to do is have a cell in my "main flash" sheet display the total sales for the week from the start of the week up until the date listed in A1. So for example since I have 3/22/06 listed in the daily date and 3/24/06 listed in the weekly end date the formula would need to display the value of 3/18 (the start of the week) threw 3/22/06. So that would be 20+10+25+20+15 for a value of 90. Another example would be if I changed the daily date to 3/13/06 and the weekly end date to 3/17/06 the value would be 35 (10+15+10). If anyone can help me out with this I'd greatly appreciate it. -- Weasel ------------------------------------------------------------------------ Weasel's Profile: http://www.excelforum.com/member.php...o&userid=27206 View this thread: http://www.excelforum.com/showthread...hreadid=525297 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to find out a solution for a complex formula
Hi,
Try the following formula: =SUM(INDIRECT("Actuals!B"&MATCH(B1,Actuals!A1:A16, 0)-6):INDIRECT("Actuals!B"&MATCH(A1,Actuals!A1:A16,0) )) Regards, B. R. Ramachandran "Weasel" wrote: I've working with two different sheets in the same workbook. The first sheet is called "main flash" and the second is called "actuals". The "main flash sheet is basically just a cover page the has totals for a day, week, month, a year. The "actuals" sheet has all of the numbers. My "actuals" sheet looks like this: Code: -------------------- Column A Date Sales 3/11 10 3/12 15 3/13 10 3/14 20 3/15 30 3/16 20 3/17 30 Total 135 3/18 20 3/19 10 3/20 25 3/21 20 3/22 15 3/23 20 3/24 30 Total 140 -------------------- The Total's listed are for each week. In my "main flash" sheet I've got the date I want information from as well as the last day of that week. So for example today I have 3/22/06 listed in cell A1 and 3/24/06 listed in cell B1. What I'm hoping to do is have a cell in my "main flash" sheet display the total sales for the week from the start of the week up until the date listed in A1. So for example since I have 3/22/06 listed in the daily date and 3/24/06 listed in the weekly end date the formula would need to display the value of 3/18 (the start of the week) threw 3/22/06. So that would be 20+10+25+20+15 for a value of 90. Another example would be if I changed the daily date to 3/13/06 and the weekly end date to 3/17/06 the value would be 35 (10+15+10). If anyone can help me out with this I'd greatly appreciate it. -- Weasel ------------------------------------------------------------------------ Weasel's Profile: http://www.excelforum.com/member.php...o&userid=27206 View this thread: http://www.excelforum.com/showthread...hreadid=525297 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to find out a solution for a complex formula
In your "main flash" sheet:
=SUMPRODUCT(actuals!B1:B100,actuals!A1:A100=B1-6,actuals!A1:A100<=A1) HTH Kostis Vezerides |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to find out a solution for a complex formula
Thanks for all of the help. It worked great! -- Weasel ------------------------------------------------------------------------ Weasel's Profile: http://www.excelforum.com/member.php...o&userid=27206 View this thread: http://www.excelforum.com/showthread...hreadid=525297 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Can't find formula for largest value in multiple cells | New Users to Excel | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES | Excel Worksheet Functions |