Function Problem

[thermalman]

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 anothe
worksheet that has a column for each month and i want it to give th
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 ho
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
Thermalma

 

[isabelle]

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 :

 

[isabelle]

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 :

 

[thermalman]

Hello Isobelle, I have added your formula to worksheet 2, Cell B2 onl
but it is giving me an error. Can you have a look at the workshee
attached and let me know where I am going wrong.


Many Thanks
Thermalma

+-------------------------------------------------------------------
|Filename: FunctionExample.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=290
+-------------------------------------------------------------------

 

[isabelle]

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 :

 

[isabelle]

here is your file with the example

http://cjoint.com/?BBhvuC489Qv

--
isabelle



Le 2012-02-07 15:08, isabelle a écrit :

 

[thermalman]

Hello Isobelle,
Many Thanks for the help on this problem, you
solution worked a treat. Your the best

Regards
Thermalma

+-------------------------------------------------------------------
+-------------------------------------------------------------------