VLOOKUP, SUM, OFFSET and IF formula all at once

[redb]

I'm not sure if this is even possible, but I'm going to ask anyway.

I use VLOOKUP to find the day's sales, which populates a different
worksheet. My main data is setup as below.

My problem is that I would like to view the cumulative weekly sales, and
would like to use one formula to do that. So far, it has gotten incredibly
complex, and I still don't have it the way I would like.

For example, to find the cumulative week to date total of Column Food, I
have written the following formula:

=SUM(OFFSET(C3,0,0,IF(WEEKDAY(OFFSET(C3,0,-2))=4,-3),1))
which gives me $2062.30 (458.5+313.4+1290.4). I thought I would write an
imbedded IF(WEEKDAY) formula, but that still leaves me with the problem of
the static C3 cell. I want to say "take the date I enter manually in cell
A1, look for it in this range, and then sum up to that date all the
information entered into that column for that week." Is that even possible?
I don't care how complex the formula itself is, I just want it to work.

Date Day Food Liquor Tobacco Covers
28-Mar-05 Mon 458.50 188.00 0.00 14.00
29-Mar-05 Tue 313.40 209.25 0.00 10.00
30-Mar-05 Wed 1290.40 1021.50 0.00 38.00
31-Mar-05 Thu 1556.65 624.25 0.00 48.00
1-Apr-05 Fri 975.20 466.00 0.00 33.00
2-Apr-05 Sat 1478.05 937.00 0.00 49.00
3-Apr-05 Sun 0.00 0.00 0.00 0.00

If anyone out there can provide some insight, it would be most appreciated!
Thanks!

[bpeltzer]

I'd be inclined to calculate the date range based on the value in A1, then
use the begin/end dates in sumif:
=sumif(a10:a1000,">="&(a1+1-weekday(a1)),c10:c1000) -
sumif(a10:a1000,">="&(a1+8-weekday(a1)),c10:c1000).
(a1+1-weekday(a1)) gives me the start of the week, so the first sumif will
add up all the food sales (column c) since the start of the selected week.
(a1+8-weekday(a1)) gives me the start of the next week, so the second sumif
adds up all food sales from subsequent weeks. The difference between the two
is the food sales for the selected week.
HTH. --Bruce

"redb" wrote:

> I'm not sure if this is even possible, but I'm going to ask anyway.
>
> I use VLOOKUP to find the day's sales, which populates a different
> worksheet. My main data is setup as below.
>
> My problem is that I would like to view the cumulative weekly sales, and
> would like to use one formula to do that. So far, it has gotten incredibly
> complex, and I still don't have it the way I would like.
>
> For example, to find the cumulative week to date total of Column Food, I
> have written the following formula:
>
> =SUM(OFFSET(C3,0,0,IF(WEEKDAY(OFFSET(C3,0,-2))=4,-3),1))
> which gives me $2062.30 (458.5+313.4+1290.4). I thought I would write an
> imbedded IF(WEEKDAY) formula, but that still leaves me with the problem of
> the static C3 cell. I want to say "take the date I enter manually in cell
> A1, look for it in this range, and then sum up to that date all the
> information entered into that column for that week." Is that even possible?
> I don't care how complex the formula itself is, I just want it to work.
>
> Date Day Food Liquor Tobacco Covers
> 28-Mar-05 Mon 458.50 188.00 0.00 14.00
> 29-Mar-05 Tue 313.40 209.25 0.00 10.00
> 30-Mar-05 Wed 1290.40 1021.50 0.00 38.00
> 31-Mar-05 Thu 1556.65 624.25 0.00 48.00
> 1-Apr-05 Fri 975.20 466.00 0.00 33.00
> 2-Apr-05 Sat 1478.05 937.00 0.00 49.00
> 3-Apr-05 Sun 0.00 0.00 0.00 0.00
>
> If anyone out there can provide some insight, it would be most appreciated!
> Thanks!