Can someone explain to me how my sumproduct formula works?

rob36

Board Regular
Joined
Jul 12, 2010
Messages
57
=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
ABCDEFGHI
11
2
3LOAD
4LOADTRIPZNBINDOORDIVITM
5DTNONONONONONOTICKETS
6-------------------------------------------------------
78/5/201010B717.94E+0865671164311
88/5/201010A097.81E+0866642701211
98/5/201010B717.91E+08726092180545
108/5/201010AJP7.91E+0874606218741
118/5/201010A097.65E+0874642701512
128/5/201010A097.65E+0874646946831
138/5/20106A097.65E+0874642701812
148/5/20106A097.65E+0874609593551
158/5/20106A097.65E+0874642711231
168/5/20106A097.65E+087460912552
178/5/20106A097.65E+0874642701011
188/5/20106A097.81E+0874609139901
Query Info
Excel 2002



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

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

rob36

Board Regular
Joined
Jul 12, 2010
Messages
57
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.
 
Last edited:

tbablue

Active Member
Joined
Apr 29, 2007
Messages
450
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

Active Member
Joined
Apr 29, 2007
Messages
450

ADVERTISEMENT

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

Board Regular
Joined
Jul 12, 2010
Messages
57
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

Board Regular
Joined
Jul 12, 2010
Messages
57
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

Board Regular
Joined
Jul 12, 2010
Messages
57
to see how my code is referencing the query, i included this little piece as well:

Excel Workbook
LMNOP
1Query Info Tickets
2total575859
3A09732000
4AA20000
5AB18000
6AC20000
Code
Excel 2002
Cell Formulas
RangeFormula
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
 
Last edited:

rob36

Board Regular
Joined
Jul 12, 2010
Messages
57
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,133,160
Messages
5,657,165
Members
418,363
Latest member
Debating_Earth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top