SUMIF with multiple parameters

[nicolemw]

I am working on a project where I need to track the totals, but some of my parameters are not numerical, giving me the #VALUE! error... how can I get around this? For example, this is the information I am working with...


LINE 11 | LINE 12
COLOR | MOLD | CASES | MOLD | CASES
Antique | 1101 | 14,784 | 2101 | 14,256
Antique | 1101 | 14,784 | 2101 | 14,256
Chmpgn | 1101 | 14,784 | 2101 | 14,256


There are 3 glass colors total, and about 13 different molds. The molds are run on two different lines of the machinery. I need to calculate the totals of each mould by color. However, if there is a mold change and/or color change, simply doing a SUMIF does not work because then I have to manually change the column restrictions each time... am I making sense? The formula I am attempting is something like this, but I cannot figure out where the error is (or rather, how to fix it)...
=SUMIF(B3:B5, "1101", C3:C5)(D3:D5, "1101", E3:E5),=IF(A3:A5,"Antique",0)

 

[VoG]

Hello and welcome to MrExcel.

Is this what you are looking for?

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Antique</td><td style="text-align:right; ">1101</td><td style="text-align:right; ">14,784</td><td style="text-align:right; ">2101</td><td style="text-align:right; ">14,256</td><td > </td><td style="text-align:right; ">29568</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Antique</td><td style="text-align:right; ">1101</td><td style="text-align:right; ">14,784</td><td style="text-align:right; ">2101</td><td style="text-align:right; ">14,256</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Chmpgn</td><td style="text-align:right; ">1101</td><td style="text-align:right; ">14,784</td><td style="text-align:right; ">2101</td><td style="text-align:right; ">14,256</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Antique</td><td style="text-align:right; ">1000</td><td style="text-align:right; ">14,784</td><td style="text-align:right; ">2101</td><td style="text-align:right; ">14,256</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >=SUMPRODUCT(--<span style=' color:008000; '>(A1:A100="Antique")</span>,--<span style=' color:008000; '>(B1:B100=1101)</span>,C1:C100)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[nicolemw]

Hello and welcome to MrExcel.

Is this what you are looking for?

Sheet3

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Antique</TD><TD style="TEXT-ALIGN: right">1101</TD><TD style="TEXT-ALIGN: right">14,784</TD><TD style="TEXT-ALIGN: right">2101</TD><TD style="TEXT-ALIGN: right">14,256</TD><TD> </TD><TD style="TEXT-ALIGN: right">29568</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Antique</TD><TD style="TEXT-ALIGN: right">1101</TD><TD style="TEXT-ALIGN: right">14,784</TD><TD style="TEXT-ALIGN: right">2101</TD><TD style="TEXT-ALIGN: right">14,256</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Chmpgn</TD><TD style="TEXT-ALIGN: right">1101</TD><TD style="TEXT-ALIGN: right">14,784</TD><TD style="TEXT-ALIGN: right">2101</TD><TD style="TEXT-ALIGN: right">14,256</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Antique</TD><TD style="TEXT-ALIGN: right">1000</TD><TD style="TEXT-ALIGN: right">14,784</TD><TD style="TEXT-ALIGN: right">2101</TD><TD style="TEXT-ALIGN: right">14,256</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G2</TD><TD>=SUMPRODUCT(--(A1:A100="Antique"),--(B1:B100=1101),C1:C100)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Excel tables to the web - Excel Jeanie Html 4

It could be... however, if there is the possiblity that the mold#1101 could show in both columns B and D, can you include parameters for that, as well as a sum for columns C and E?

 

[nicolemw]

=SUMPRODUCT(--(C5:C369="Antique"),--(D5:D369=1101),E5:E369,--(G5:G369=1101),H5:H369)

This is what I tried and it is giving me a 0 (zero) output... which is not what I was looking for. What am I doing wrong?

 

[VoG]

Try

=SUMPRODUCT(--(A1:A100="Antique"),--(B1:B100=1101)+(D1:D100=1101),C1:C100+E1:E100)

 

[DonkeyOte]

assuming you want to Sum E only if D matches criteria... ie not an OR per se perhaps:

=SUMPRODUCT(--(A1:A100="Antique"),((B1:B100=1101)*(C1:C100))+((D1:D100=1101)*(E1:E100)))

 

[nicolemw]

For some reason it is counting everything twice... my total is exactly doubled what it should be. Any ideas? Sorry to be a pain!

 

[DonkeyOte]

Which formula are you using... it's possible that VoG's will double.

 

[nicolemw]

assuming you want to Sum E only if D matches criteria... ie not an OR per se perhaps:

=SUMPRODUCT(--(A1:A100="Antique"),((B1:B100=1101)*(C1:C100))+((D1:D100=1101)*(E1:E100)))

BEAUTIFUL.

I KNEW there had to be a way to do it! Thank you