# 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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

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

Replies
0
Views
25
Replies
8
Views
144
Replies
3
Views
154
Replies
3
Views
44
Replies
4
Views
54