SUMIF with multiple criteria needed

rob36

Board Regular
Joined
Jul 12, 2010
Messages
57
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
AAABACADAEAFAGAH
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)
AD6=SUMIF('Query Info'!$F:$F,'Purdue Actual Tool'!AD5,'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
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/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...
 

Some videos you may like

Excel Facts

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

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,543
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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
Joined
Jul 12, 2010
Messages
57
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
Joined
Jan 15, 2008
Messages
3,543
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,841
Messages
5,525,166
Members
409,629
Latest member
McGuilliam

This Week's Hot Topics

Top