Re: Sum Activecell Offset Problem
- From: "Bob Phillips" <bob.phillips@xxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 20 May 2005 23:50:24 +0100
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
>
>
.
- Follow-Ups:
- Re: Sum Activecell Offset Problem
- From: George Andrews
- Re: Sum Activecell Offset Problem
- References:
- Sum Activecell Offset Problem
- From: George Andrews
- Sum Activecell Offset Problem
- Prev by Date: Re: average of visible cells in a filtered range
- Next by Date: Re: finding the no. of rows in a COL filled with numbers, zeros and bl
- Previous by thread: Sum Activecell Offset Problem
- Next by thread: Re: Sum Activecell Offset Problem
- Index(es):
Relevant Pages
|
