SUMIF with multiple criteria needed

R

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

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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)
 
Upvote 0
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 :) )
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
176
bh24524
B
E
  • Solved
#=SORT(UNIQUE(FILTER(A2:E10,{0,1,1,0,1})))
Replies
9
Views
200
Fluff
Fluff
E
  • Solved
Count how many qty's are greater than "0" (or blank) which make the SUMIF total
Replies
7
Views
372
dreid1011
dreid1011
I
  • Solved
Have Listbox show dd/mm/yyy
Replies
2
Views
274
ipbr21054
I
excelbytes
  • Solved
SUMIFS With Multiple Criteria In One Column
2 3
Replies
26
Views
1K
KRice
K

Forum statistics

Threads
1,214,659
Messages
6,120,786
Members
448,993
Latest member
Seri

Share this page

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
Back
Top