Exell 2000

Dear All

I have three columns of data. Day of the week, Date, Product Sales

What I need is a function that can work out the total sales for a 7 day
period finishing on a Sunday (Sun). i.e The total sales from Monday to
Sunday. The problem is that there could be more than one entry per day.
e.g. there could be two Thursdays in one week with the same date.

Therefore the formula will have to look at the Sunday date subract 7 days
and add up the values in the third column.

if it is easier I could work around my problem by using a formula that
calculates the total from the previous seven days baring in mind that there
could be more than one entry per day.

Here is some sample data. (European date system)

S 28/5 0.00
Sun 29/5 2,900.00
M 30/5 4,279.24
T 31/5 3 ,016.00
W 1/6 850.28
Th 2/6 0.00
F 3/6 5,775.64
S 4/6 9,210.40
Sun 5/6 2,900.00

If anybody can help, it would be appreciated.

Regards

Andrew

Andrew,

Try this

=SUMPRODUCT(--(B1:B1000MAX(IF(A1:A1000="Sun",B1:B1000))-7),--(B1:B1000<=MAX
(IF(A1:A9="Sun",B1:B1000))),C1:C1000)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Andrews" wrote in message
...
Exell 2000

Dear All

I have three columns of data. Day of the week, Date, Product Sales

What I need is a function that can work out the total sales for a 7 day
period finishing on a Sunday (Sun). i.e The total sales from Monday to
Sunday. The problem is that there could be more than one entry per day.
e.g. there could be two Thursdays in one week with the same date.

Therefore the formula will have to look at the Sunday date subract 7 days
and add up the values in the third column.

if it is easier I could work around my problem by using a formula that
calculates the total from the previous seven days baring in mind that
there
could be more than one entry per day.

Here is some sample data. (European date system)

S 28/5 0.00
Sun 29/5 2,900.00
M 30/5 4,279.24
T 31/5 3 ,016.00
W 1/6 850.28
Th 2/6 0.00
F 3/6 5,775.64
S 4/6 9,210.40
Sun 5/6 2,900.00

If anybody can help, it would be appreciated.

Regards

Andrew

Bob

Hi again.

This has not worked.

I have been looking at the formula and I have to admit that I don't
understand most of it.

Why do you have the range A1:A9?
What do the two -- mean?

I have also noticed that :

if I put the formula in the intended worksheet in column C, I get a cicrular
referernce
if I put the formula in row 9 then I get a value error
I set up the information starting in A1 and put the formula into F1 with Sun
in A1. The formula produces the correct result.. The next occurrence of
"Sun" is in A8. the formula in F8 does not produce the same result. I have
tried copying the formula down and have also tried pasting the same formula
into the cell F8. Neither work.

Can you give me some further guidance.

Regards

George




"Bob Phillips" wrote in message
...
Andrew,

Try this


=SUMPRODUCT(--(B1:B1000MAX(IF(A1:A1000="Sun",B1:B1000))-7),--(B1:B1000<=MAX
(IF(A1:A9="Sun",B1:B1000))),C1:C1000)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Andrews" wrote in message
...
Exell 2000

Dear All

I have three columns of data. Day of the week, Date, Product Sales

What I need is a function that can work out the total sales for a 7 day
period finishing on a Sunday (Sun). i.e The total sales from Monday to
Sunday. The problem is that there could be more than one entry per day.
e.g. there could be two Thursdays in one week with the same date.

Therefore the formula will have to look at the Sunday date subract 7
days
and add up the values in the third column.

if it is easier I could work around my problem by using a formula that
calculates the total from the previous seven days baring in mind that
there
could be more than one entry per day.

Here is some sample data. (European date system)

S 28/5 0.00
Sun 29/5 2,900.00
M 30/5 4,279.24
T 31/5 3 ,016.00
W 1/6 850.28
Th 2/6 0.00
F 3/6 5,775.64
S 4/6 9,210.40
Sun 5/6 2,900.00

If anybody can help, it would be appreciated.

Regards

Andrew

Hi George,

I made an error in the formula, and omitted to tell you something important,
so it is not surprising that you struggled :-)

The A1:A9 was the error. In my testing I used rows 1-9 then changed it to
1-1000 for you, but left that one unchanged. It also should be A1:A1000/

I also forgot to tell you that it is an array formula, so you need to commit
it after inputting it with Ctrl-Shift-Enter.

I used rows 1-1000, but you should set it at the maximum that you know will
be there. You cannot put the formula in column C in any row between 1 and
the max row, but any other column is fine.

The formula is

=SUMPRODUCT(--(B1:BnMAX(IF(A1:An="Sun",B1:Bn))-7),--(B1:Bn<=MAX(IF(A1:An="S
un",B1:Bn))),C1:Cn)

where n is the max row number and is array entered.

In summary, this is what it does
- gets the Max date for all rows where column A is Sun -
MAX(IF(A1:An="Sun",B1:Bn)
- counts all dates that are greater than that max date - 7 days -
B1:BnMAX(IF(A1:An="Sun",B1:Bn))-7
- and which are also les than or equal to that max date -
B1:Bn<=MAX(IF(A1:An="Sun",B1:Bn)))
- sums all amounts for matching dates - SUMPRODUCT(...,...,C1:Cn)

For an explanation of how SUMPRODUCT works, and the double unary --, see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards

Bob


"George Andrews" wrote in message
...
Bob

Hi again.

This has not worked.

I have been looking at the formula and I have to admit that I don't
understand most of it.

Why do you have the range A1:A9?
What do the two -- mean?

I have also noticed that :

if I put the formula in the intended worksheet in column C, I get a
cicrular
referernce
if I put the formula in row 9 then I get a value error
I set up the information starting in A1 and put the formula into F1 with
Sun
in A1. The formula produces the correct result.. The next occurrence of
"Sun" is in A8. the formula in F8 does not produce the same result. I
have
tried copying the formula down and have also tried pasting the same
formula
into the cell F8. Neither work.

Can you give me some further guidance.

Regards

George




"Bob Phillips" wrote in message
...
Andrew,

Try this



=SUMPRODUCT(--(B1:B1000MAX(IF(A1:A1000="Sun",B1:B1000))-7),--(B1:B1000<=MAX
(IF(A1:A9="Sun",B1:B1000))),C1:C1000)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Andrews" wrote in message
...
Exell 2000

Dear All

I have three columns of data. Day of the week, Date, Product Sales

What I need is a function that can work out the total sales for a 7
day
period finishing on a Sunday (Sun). i.e The total sales from Monday to
Sunday. The problem is that there could be more than one entry per
day.
e.g. there could be two Thursdays in one week with the same date.

Therefore the formula will have to look at the Sunday date subract 7
days
and add up the values in the third column.

if it is easier I could work around my problem by using a formula that
calculates the total from the previous seven days baring in mind that
there
could be more than one entry per day.

Here is some sample data. (European date system)

S 28/5 0.00
Sun 29/5 2,900.00
M 30/5 4,279.24
T 31/5 3 ,016.00
W 1/6 850.28
Th 2/6 0.00
F 3/6 5,775.64
S 4/6 9,210.40
Sun 5/6 2,900.00

If anybody can help, it would be appreciated.

Regards

Andrew