|
SUMIF/OFFSET Query
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? |
SUMIF/OFFSET Query
Why not just create a pivot table?
Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "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? |
SUMIF/OFFSET Query
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? |
SUMIF/OFFSET Query
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? |
| All times are GMT +1. The time now is 12:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com