|
Function Problem
Hi, I have an Excel Spreadsheet that has the following columns
Part No - Quantity Rejected - Date Rejected - Supplier 1234 1 01/01/2012 Joes Hardware 2345 3 12/02/2012 Freds Spares 1378 5 19/03/2012 Rocky Spares I want to write a function that will return a figure into another worksheet that has a column for each month and i want it to give the quantity Rejected by Supplier and by Month Supplier JAN - FEB- MAR - APR Joes Hardware 1 Freds Spares 3 Rocky Spares 5 Can anybody tell me if this is possible and give me some idea as to how to write the formula.. I am quite a newbie to Excel functions. Sorry but the 1 should be under Jan, 3 under Feb and the 5 under Mar. Regards Thermalman |
Function Problem
hi Thermalman,
on Sheet2 put in cell B2:B13: 1900-01-01 - 1900-02-01 - 1900-03-01 - etc.. Format "mmm" put in cell A2:Ax : Supplier in cell B2: the formula: =SUMPRODUCT(--(Sheet1!$D$1:$D$20=$A2)*(MONTH(Sheet1!$C$1:$C$20)= MONTH(B$1))*(Sheet1!$B$1:$B$20)) -- isabelle Le 2012-02-02 16:46, thermalman a écrit : Hi, I have an Excel Spreadsheet that has the following columns Part No - Quantity Rejected - Date Rejected - Supplier 1234 1 01/01/2012 Joes Hardware 2345 3 12/02/2012 Freds Spares 1378 5 19/03/2012 Rocky Spar1900-02-01es I want to write a function that will return a figure into another worksheet that has a column for each month and i want it to give the quantity Rejected by Supplier and by Month Supplier JAN - FEB- MAR - APR Joes Hardware 1 Freds Spares 3 Rocky Spares 5 Can anybody tell me if this is possible and give me some idea as to how to write the formula.. I am quite a newbie to Excel functions. Sorry but the 1 should be under Jan, 3 under Feb and the 5 under Mar. Regards Thermalman |
Function Problem
correction:
on Sheet2 put in cell B1:M1: 1900-01-01 - 1900-02-01 - 1900-03-01 - etc.. Format "mmm" put in cell A2:Ax : Supplier in cell B2: the formula: =SUMPRODUCT(--(Sheet1!$D$1:$D$20=$A2)*(MONTH(Sheet1!$C$1:$C$20)= MONTH(B$1))*(Sheet1!$B$1:$B$20)) -- isabelle Le 2012-02-02 21:38, isabelle a écrit : hi Thermalman, on Sheet2 put in cell B2:B13: 1900-01-01 - 1900-02-01 - 1900-03-01 - etc.. Format "mmm" put in cell A2:Ax : Supplier in cell B2: the formula: =SUMPRODUCT(--(Sheet1!$D$1:$D$20=$A2)*(MONTH(Sheet1!$C$1:$C$20)= MONTH(B$1))*(Sheet1!$B$1:$B$20)) |
1 Attachment(s)
Hello Isobelle, I have added your formula to worksheet 2, Cell B2 only but it is giving me an error. Can you have a look at the worksheet attached and let me know where I am going wrong.
Many Thanks Thermalman |
Function Problem
hi ,
you should putting real date in cell B1:M1 (1900-01-01, 1900-02-01...) and then put those twelve cells in the format "mmm" -- isabelle Le 2012-02-07 14:36, thermalman a écrit : Hello Isobelle, I have added your formula to worksheet 2, Cell B2 only but it is giving me an error. Can you have a look at the worksheet attached and let me know where I am going wrong. Many Thanks Thermalman +-------------------------------------------------------------------+ |Filename: FunctionExample.zip | |Download: http://www.excelbanter.com/attachment.php?attachmentid=290| +-------------------------------------------------------------------+ |
Function Problem
here is your file with the example
http://cjoint.com/?BBhvuC489Qv -- isabelle Le 2012-02-07 15:08, isabelle a écrit : hi , you should putting real date in cell B1:M1 (1900-01-01, 1900-02-01...) and then put those twelve cells in the format "mmm" |
Hello Isobelle,
Many Thanks for the help on this problem, your solution worked a treat. Your the best Regards Thermalman |
| All times are GMT +1. The time now is 10:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com