Hi,

Ok...try this.

On sheet one paste the following in a spare column (change A1 to the cell
which containts the date):

=ROUNDUP((A1-38717)/7,0)

Then, on sheet 2 paste the same and change A1 to the START date of the week
in your list.

then in a spare column on sheet2 paste the following (change "Sheet1!B1:B10"
to the cells which contain the calculated week numbers on the first sheet ,
"sheet2!b1" to the cell which contains week number on the second sheet,
"Sheet1!C1:C10" to the dails sales figures on the first sheet"):

=SUMIF(Sheet1!$B$1:$B$10,"="&Sheet2!B1,Sheet1!$C$1 :$C$10)


A more efficient way, however, would be to calculate the week on sheet 1 the
create a pivot table for the sum by week calculation.

Thanks,

Simon



vivi wrote:
Hi

thanks for your post and your suggestions but unfortunately I've tried it
but it doesn't work for me, maybe I've haven't explained myself correctly

The first sheet contained a daily sales figure from 01/01/06 to 30/09/06

E.G.

01/01/06 £500
02/01/06 £600
03/01/06 £400
04/01/06 £600
05/01/06 £700
06/01/06 £500
07/01/06 £400

What I want to do is that from my lookup table in the 2nd Sheet, I have this
figures:

To From Week No.
31/12/2005 06/01/2006 1
07/01/2006 13/01/2006 2
14/01/2006 20/01/2006 3
21/01/2006 27/01/2006 4
28/01/2006 03/02/2006 5

So on my first sheet I would like to have a column to show me which number
of week does a day falls into e.g.
Date: £: Week no.
01/01/06 £500 1
02/01/06 £600 1
03/01/06 £400 1
04/01/06 £600 1
05/01/06 £700 1
06/01/06 £500 1
07/01/06 £400 1
08/01/06 £500 2

I've used your formula but I could onlu get an answer to the first cell but
then all the others are incorrectly showing zeros, is there other formulas
that I can use?

Thanks a lot

Vivi

=SUMPRODUCT(--(Sales!A1:A1000=A2),--(Sales!A1:A1000<=B2),Sales!B1:B1000)

[quoted text clipped - 27 lines]

Vivi

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via http://www.officekb.com