Re: Sum Activecell Offset Problem

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:Bn>MAX(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:Bn>MAX(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" <abctravel@xxxxxxxxx> wrote in message
news:uoyNKekXFHA.2768@xxxxxxxxxxxxxxxxxxxxxxx
> 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 work*** 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" <bob.phillips@xxxxxxxxxxxxxxxxxxxx> wrote in message
> news:OxcMQ5YXFHA.3988@xxxxxxxxxxxxxxxxxxxxxxx
> > Andrew,
> >
> > Try this
> >
> >
>
=SUMPRODUCT(--(B1:B1000>MAX(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" <abctravel@xxxxxxxxx> wrote in message
> > news:O%23IlqjYXFHA.2420@xxxxxxxxxxxxxxxxxxxxxxx
> > > 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
> > >
> > >
> >
> >
>
>


.