Can someone explain to me how my sumproduct formula works?

[rob36]

=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L32),--('Query Info'!$F$1:$F$65000=Code!N$2,'Query Info'!$I$1:$I$65000)

Column D is a zone number, F is a door number, and I is ticket numbers. It currently takes each zone, breaks it down by door, and lists the total number of tickets. I need to add a feature so that an additional column (BATCH NUMBER), will be looked into as well. By this, I mean that it will not sum the tickets unless the batch number is = to a certain value.

Thanks!

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	1
2
3						LOAD
4		LOAD	TRIP	ZN	BIN	DOOR	DIV	ITM
5		DT	NO	NO	NO	NO	NO	NO	TICKETS
6		----------	----	---	------------	----	----	-------	-----------
7		8/5/2010	10	B71	7.94E+08	65	671	16431	1
8		8/5/2010	10	A09	7.81E+08	66	642	70121	1
9		8/5/2010	10	B71	7.91E+08	72	609	21805	45
10		8/5/2010	10	AJP	7.91E+08	74	606	21874	1
11		8/5/2010	10	A09	7.65E+08	74	642	70151	2
12		8/5/2010	10	A09	7.65E+08	74	646	94683	1
13		8/5/2010	6	A09	7.65E+08	74	642	70181	2
14		8/5/2010	6	A09	7.65E+08	74	609	59355	1
15		8/5/2010	6	A09	7.65E+08	74	642	71123	1
16		8/5/2010	6	A09	7.65E+08	74	609	1255	2
17		8/5/2010	6	A09	7.65E+08	74	642	70101	1
18		8/5/2010	6	A09	7.81E+08	74	609	13990	1
Query Info

Excel 2002



this is a little view of the data i'm dealing with.

 

[tbablue]

What column is your Batch number info kept in?

 

[rob36]

I'm sorry, batch number is referred to as trip number in the query... It is in column C

for instance, if the batch entered was 10, then I would want A09, door 74 to display a value of 3 tickets (2+1)

keep in mind that i have thousands of rows for data, this is just a very small picture.

 

[tbablue]

maybe I'm misunderstanding your question but isn't it as simple as

=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L32),--('Query Info'!$F$1:$F$65000=Code!N$2),),--('Query Info'!$C$1:$C$65000=your value here)'Query Info'!$I$1:$I$65000)

 

[tbablue]

sorry, missed a comma

=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L32),--('Query Info'!$F$1:$F$65000=Code!N$2),),--('Query Info'!$C$1:$C$65000=your value here),'Query Info'!$I$1:$I$65000)

 

[rob36]

I think it's something very close to that, however I get the issue when I try

=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L32),--('Query Info'!$F$1:$F$65000=Code!N$2),),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!G1),'Query Info'!$I$1:$I$65000)

of the last part, 'Query Info'!$I$1:$I$65000 highlighting $I$65000 and saying there's a forumla error.

 

[barry houdini]

You still have a typo, remove the red part

=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L32),--('Query Info'!$F$1:$F$65000=Code!N$2),),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!G1),'Query Info'!$I$1:$I$65000)

 

[rob36]

It must be too early considering I didn't catch that. Anyways, what I have now is:

=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L32),--('Query Info'!$F$1:$F$65000=Code!N$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)

this gives me a value of 0. Obviously this is incorrect becauase look at even that little piece of the query would get me a value of 3 (I think the number should probably be somewhere around 20-30 when the entire query is included)

 

[rob36]

to see how my code is referencing the query, i included this little piece as well:

Excel Workbook
	L	M	N	O	P
1		Query Info Tickets
2		total	57	58	59
3	A09	732	0	0	0
4	AA2	0	0	0	0
5	AB1	8	0	0	0
6	AC2	0	0	0	0
Code

Excel 2002

Cell Formulas
Range	Formula
M3	=SUMIF('Query Info'!D:D,L3,'Query Info'!I:I)
M4	=SUMIF('Query Info'!D:D,L4,'Query Info'!I:I)
M5	=SUMIF('Query Info'!D:D,L5,'Query Info'!I:I)
M6	=SUMIF('Query Info'!D:D,L6,'Query Info'!I:I)
N3	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L32),--('Query Info'!$F$1:$F$65000=Code!N$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)
N4	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L33),--('Query Info'!$F$1:$F$65000=Code!N$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)
N5	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L34),--('Query Info'!$F$1:$F$65000=Code!N$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)
N6	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L35),--('Query Info'!$F$1:$F$65000=Code!N$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)
O3	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L32),--('Query Info'!$F$1:$F$65000=Code!O$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)
O4	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L33),--('Query Info'!$F$1:$F$65000=Code!O$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)
O5	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L34),--('Query Info'!$F$1:$F$65000=Code!O$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)
O6	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L35),--('Query Info'!$F$1:$F$65000=Code!O$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)
P3	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L32),--('Query Info'!$F$1:$F$65000=Code!P$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)
P4	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L33),--('Query Info'!$F$1:$F$65000=Code!P$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)
P5	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L34),--('Query Info'!$F$1:$F$65000=Code!P$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)
P6	=SUMPRODUCT(--('Query Info'!$D$1:$D$65000=Code!$L35),--('Query Info'!$F$1:$F$65000=Code!P$2),--('Query Info'!$C$1:$C$65000= 'Purdue Actual Tool'!$G$1),'Query Info'!$I$1:$I$65000)

in that code, L are different zones, and 2 are different ship doors

 

[rob36]

Additionally, if SUMPRODUCT isn't what I should be using, please feel free to change the code (not sure if a nested if statement, or a SUMIF would be more useful in this instance)