| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| MS Query: Looking for a work-around to 2-table limits for outer jo | Excel Discussion (Misc queries) | |||
| Problems importing from an Access query | Excel Discussion (Misc queries) | |||
| changing query source | Excel Worksheet Functions | |||
| AHHH! Again | Excel Discussion (Misc queries) | |||
| AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
Linear Mode
