# SUMIF with multiple criteria needed

#### rob36

##### Board Regular
To sum it up, I basically have a sumif statement that works fine, however I need to add an additional paremeter that not only must one column match for the values to be summed, two different columns have to be correct.

Hi, the code I currently have is as follows:

Excel Workbook
LMNOPQ
1Query Info Tickets
2total57585960
3A097323015022
4AA200000
5AB180000
6AC200000
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!\$L3),--('Query Info'!\$F\$1:\$F\$65000=Code!N\$2),'Query Info'!\$I\$1:\$I\$65000)
N4=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L4),--('Query Info'!\$F\$1:\$F\$65000=Code!N\$2),'Query Info'!\$I\$1:\$I\$65000)
N5=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L5),--('Query Info'!\$F\$1:\$F\$65000=Code!N\$2),'Query Info'!\$I\$1:\$I\$65000)
N6=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L6),--('Query Info'!\$F\$1:\$F\$65000=Code!N\$2),'Query Info'!\$I\$1:\$I\$65000)
O3=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L3),--('Query Info'!\$F\$1:\$F\$65000=Code!O\$2),'Query Info'!\$I\$1:\$I\$65000)
O4=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L4),--('Query Info'!\$F\$1:\$F\$65000=Code!O\$2),'Query Info'!\$I\$1:\$I\$65000)
O5=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L5),--('Query Info'!\$F\$1:\$F\$65000=Code!O\$2),'Query Info'!\$I\$1:\$I\$65000)
O6=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L6),--('Query Info'!\$F\$1:\$F\$65000=Code!O\$2),'Query Info'!\$I\$1:\$I\$65000)
P3=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L3),--('Query Info'!\$F\$1:\$F\$65000=Code!P\$2),'Query Info'!\$I\$1:\$I\$65000)
P4=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L4),--('Query Info'!\$F\$1:\$F\$65000=Code!P\$2),'Query Info'!\$I\$1:\$I\$65000)
P5=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L5),--('Query Info'!\$F\$1:\$F\$65000=Code!P\$2),'Query Info'!\$I\$1:\$I\$65000)
P6=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L6),--('Query Info'!\$F\$1:\$F\$65000=Code!P\$2),'Query Info'!\$I\$1:\$I\$65000)
Q3=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L3),--('Query Info'!\$F\$1:\$F\$65000=Code!Q\$2),'Query Info'!\$I\$1:\$I\$65000)
Q4=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L4),--('Query Info'!\$F\$1:\$F\$65000=Code!Q\$2),'Query Info'!\$I\$1:\$I\$65000)
Q5=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L5),--('Query Info'!\$F\$1:\$F\$65000=Code!Q\$2),'Query Info'!\$I\$1:\$I\$65000)
Q6=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L6),--('Query Info'!\$F\$1:\$F\$65000=Code!Q\$2),'Query Info'!\$I\$1:\$I\$65000)

Excel Workbook
5888786858483
60400625752
Purdue Actual Tool
Excel 2002
Cell Formulas
RangeFormula
AA6=SUMIF('Query Info'!\$F:\$F,'Purdue Actual Tool'!AA5,'Query Info'!\$I:\$I)
AB6=CellAndComment(SUMIF('Query Info'!\$F:\$F,'Purdue Actual Tool'!AB5,'Query Info'!\$I:\$I), "Mod A: "&Code!AN26&CHAR(10)&"Mod B: "&Code!AN27&CHAR(10)&"Mod C: "&Code!AN28)
AC6=SUMIF('Query Info'!\$F:\$F,'Purdue Actual Tool'!AC5,'Query Info'!\$I:\$I)
AE6=CellAndComment(SUMIF('Query Info'!\$F:\$F,'Purdue Actual Tool'!AE5,'Query Info'!\$I:\$I), "Mod A: "&Code!AM26&CHAR(10)&"Mod B: "&Code!AM27&CHAR(10)&"Mod C: "&Code!AM28)
AF6=SUMIF('Query Info'!\$F:\$F,'Purdue Actual Tool'!AF5,'Query Info'!\$I:\$I)
AG6=CellAndComment(SUMIF('Query Info'!\$F:\$F,'Purdue Actual Tool'!AG5,'Query Info'!\$I:\$I), "Mod A: "&Code!AL26&CHAR(10)&"Mod B: "&Code!AL27&CHAR(10)&"Mod C: "&Code!AL28)
AH6=CellAndComment(SUMIF('Query Info'!\$F:\$F,'Purdue Actual Tool'!AH5,'Query Info'!\$I:\$I), "Mod A: "&Code!AK26&CHAR(10)&"Mod B: "&Code!AK27&CHAR(10)&"Mod C: "&Code!AK28)

Here is a sample of the query I'm using:

Excel Workbook
ABCDEFGHI
11
2
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/201010A097.65E+0874642701812
148/5/201010A097.65E+0874609593551
158/5/201016A097.65E+0874642711231
168/5/201016A097.65E+087460912552
178/5/201016A097.65E+0874642701011
188/5/201016A097.81E+0874609139901
198/5/201016A097.81E+0874609200121
208/5/201016A097.81E+0874657501071
218/5/201016A097.81E+0874609139911
Query Info

Excel 2002

What my code currently does, is it looks at my query and sums all of the tickets based upon which door (column F) the item is going to. What I need it to do, is to only sum tickets of the same door WITH column C = to 10 (or whatever is entered in by a user in lets say, column A1)

Essentially I thought something along the lines of an If statement would work, however its almost like I need a sumif nested in a sumif? I'm not sure if thats possible though...

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### iliace

##### Well-known Member
So, if your trip # is in A1 on the Code sheet, could you do something like:

Code!N3

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

#### rob36

##### Board Regular
Thank you so much. That was exactly what I was asking. To add onto this, if I want to grant the user the ability to enter more than one batches, how would I go about doing this? Currently, they can enter in a batch number, and it will sum only those batches. If I want to make it so they could enter in up to 4 batch numbers (each can have their own column), how can I make an OR statement to make this work? (im assuming OR, please use whatever is easiest )

#### iliace

##### Well-known Member
Here is an example where trip # is either A1 or A2.

=SUMPRODUCT(--('Query Info'!\$D\$1:\$D\$65000=Code!\$L3), --('Query Info'!\$F\$1:\$F\$65000=Code!N\$2), ('Query Info'!\$C\$1:\$C\$65000=Code!\$A\$1)+('Query Info'!\$C\$1:\$C\$65000=Code!\$A\$2), 'Query Info'!\$I\$1:\$I\$65000)

To quote Mr. Excel: * is AND, + is OR, 0 is FALSE, everything else is TRUE

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,032
Messages
5,856,939
Members
431,841
Latest member
jaybeem

### 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.

### Which adblocker are you using?

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

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