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:

Please Login or Register  to view this content.

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.

In Main Flash, enter:
=SUMPRODUCT(--(actuals!A2:A16<=A1),--(actuals!A2:A16>A2-7),actuals!B2:B16)

The weekly totals in "actuals" are skipped because of the "Total" text.

HTH
--
AP

"Weasel" <[email protected]> a écrit dans
le message de news:[email protected]...
>
> 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
>

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

In your "main flash" sheet:

=SUMPRODUCT(actuals!B1:B100,actuals!A1:A100>=B1-6,actuals!A1:A100<=A1)

HTH
Kostis Vezerides