Trying to find out a solution for a complex formula

[Weasel]

I've working with two different sheets in the same workbook. The firs
sheet is called "main flash" and the second is called "actuals". Th
"main flash sheet is basically just a cover page the has totals for
day, week, month, a year. The "actuals" sheet has all of the numbers
My "actuals" sheet looks like this

Code
-------------------

Column

Date Sale
3/11 1
3/12 1
3/13 1
3/14 2
3/15 3
3/16 2
3/17 3
Total 13
3/18 2
3/19 1
3/20 2
3/21 2
3/22 1
3/23 2
3/24 3
Total 14

-------------------

The Total's listed are for each week. In my "main flash" sheet I've go
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/0
listed in cell B1

What I'm hoping to do is have a cell in my "main flash" sheet displa
the total sales for the week from the start of the week up until th
date listed in A1. So for example since I have 3/22/06 listed in th
daily date and 3/24/06 listed in the weekly end date the formula woul
need to display the value of 3/18 (the start of the week) thre
3/22/06. So that would be 20+10+25+20+15 for a value of 90. Anothe
example would be if I changed the daily date to 3/13/06 and the weekl
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

 

[Ardus Petus]

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

 

[Guest]

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

 

[vezerid]

In your "main flash" sheet:

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

HTH
Kostis Vezerides

 

[Weasel]

Thanks for all of the help. It worked great