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

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

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

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

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

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