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.

 

[nancymk]

I think you are just trying to sum the number of tickets right? So you don't need a sumproduct, because that multiples two columns and then sums them together. I would use the CSE formula.

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

After you enter the formula hit ctrl-shift-enter. Not just enter.

 

[nancymk]

I think my () might be off...

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

 

[barry houdini]

There are alternatives to SUMPRODUCT, e.g. using an "array formula" as nancymk suggests, but I'd expect both formulas to give the same results. Your SUMPRODUCT formula is valid but you probably have some sort of issue with data mismatches. Try using a cut down version of 10 rows, e.g.

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

You may need to change the data in those 10 rows to ensure that you get a >0 result.

If that still gives zero then I suggest you check each part of the formula. In the formula bar if you select each part in turn, e.g. select just

--('Query Info'!$D$1:$D$10=Code!$L32)

and press F9 key to display the results. Make sure it displays what you expect, e.g. if there are matches for that criterion in rows 2, 3 and 4 only you should see an array like this

{0;1;1;1;0;0;0;0;0;0}

In this way you can check which of the criteria are giving unexpected results, perhaps you have some spaces in the data which means you don't get a match where you expect......