Just the job!

Thanks very much for your help & also to those who also offered alternatives

"driller" wrote:

hi neil

maybe, sumproduct and pivot tables can do some trick to your inquiry also

something like this

on sheet 1 : place true dates on your DATA heading [b1:d1]
(e.g. 31-Jan-07,28-Feb-07,31-Mar-07)
looks like this format "MMM-YY"
Jan-07 Feb-07 Mar-07
ProductA 10 11 13
ProductB 15 10 5
ProductC 9 8 20
ProductA 1 2 3
ProductB 7 8 9
------------------------------------------
on sheet 2. B1 type = 28-Feb-07 [this is the search date]
: the year to date contains the sum of product data before and until
28-FEB-07..
or may i say to be the cumulative sum of product data until the search date.
Feb-07 YEAR TO DATE
ProductA 13 24
ProductB 18 40
ProductC 8 17

so for sum of product data on current month Feb-07
on B2
=SUMPRODUCT((A2=Sheet1!$A$2:$A$6)*(Sheet1!$B$1:$D$ 1=B$1)*(Sheet1!$B$2:$D$6))
copy/paste down to B4

and for the cumulative sum til month of Feb-07
on C2
=SUMPRODUCT((A2=Sheet1!$A$2:$A$6)*(Sheet1!$B$1:$D$ 1<=B$1)*(Sheet1!$B$2:$D$6))
copy/paste down to C4

just maybe possible if you try

regards
--
*****
birds of the same feather flock together..



"Neil Webber" wrote:

I have what on the face of it appears that it should be quite straight
forward but I'm struggling! I've tried using the SUMIF & OFFSET functions but
can't achieve what I'm looking for

I have a list of data in cells A1:D6 within 1 spreadsheet
Jan Feb Mar
ProductA 10 11 13
ProductB 15 10 5
ProductC 9 8 20
ProductA 1 2 3
ProductB 7 8 9

Within a second spreadsheet I have my report as follows

Month Year to date
ProductA
ProductB
ProductC

What I want to be able to do is define what the month is within a cell
(perhaps February=2 or "Feb") & for the report to populate with the given
month & Year to date data.

Hence using the Feb example I would expect

Month Year to date
ProductA 13 24
ProductB 18 40
ProductC 8 17

As you can see there can be multiple records within a given month that need
to be summed.

Can anybody help me please?