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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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
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
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)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,999
Messages
5,508,685
Members
408,690
Latest member
Lip Renan

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top