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

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

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

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

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