SUMIF/OFFSET Query

[Guest]

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 A16 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?

 

[Guest]

Why not just create a pivot table?

Dave

 

[Don Guillett]

This should get you YTD-copied to right
=SUMPRODUCT(($H$3:$H$13="producta")*$I$3:I13)

 

[Guest]

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

 

[Guest]

Just the job!

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

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..

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 A16 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?