Match, Sumif, Multi Function Formulas???

[cgreen]

Hi All, Hope you can help.

This is the formula I'm playing with:
=IF(B5>0,IF(MATCH($A$1,'[Copy of Document Register.xls]Production Control'!$B$2:$B$1001,0),SUMPRODUCT(--('[Copy of Document Register.xls]Production Control'!$C$2:$C$1001=B5),('[Copy of Document Register.xls]Production Control'!$J$2:$J$1001)),0))

The month I am working on is in A1 on the 'monthly schedule' report. I want it to look at A1 then go to the 'copy of document register' and look at column B to see if it matches A1. If column B from 'copy of document register' matches A1, I want it to look at B5 and match to column C on the 'copy of document register' and total up the numbers in column (D-E) of the 'copy of document register'.

Copy of Document Register.xls
	B	C	D	E
1	"Orders" MonthofSchd	PCN	QTYOrdered	QTYReturned
2	Jan-05	N021ZG	4
3	Jan-05	N03ZA0	5	2
4	Jan-05	N02MF5	4
5	Dec-04	N01STH	1
6	Dec-04	N01STK	1
Production Control

Monthly Schedule.xls
	A	B	C	D	E	F	G	H	I	J
1	Jan-05
2		ProgramsDatabase							DocumentRegister
3		PCN'sforFY05	Name	Type	Auth.Totals		MonthSchdRequired		MonthSchdOrdered	TotalOrdered
4	1	N01JQ0	T700Eng	OCMI0	5					--
5	2	N02JQ0	T700Eng	RecapA3	200				#REF!	76
6	3	N01BCZ	T701CEng	OCMI0	10					--
7	4	N02BCZ	T701CEng	ConvC2	50					--
8	5	N03BCZ	T701CEng	RecapA3	220					--
PC Count Report for FY05

 

[cgreen]

=SUMIF('[Copy of Document Register.xls]Production Control'!$B$2:$B$1001,A1,'[Copy of Document Register.xls]Production Control'!$D$2:$D$1001)

For the above spreadsheet: This formula gives me the total count for the date ordered. How do I get it to give me the total count only if the PCN matches to the date ordered??? ray: