Simple formula help...

[supdawg]

I am drawing a blank for some reason on how to do this.

I have 2 worksheets in my workbook. In one sheet I have data that looks like this:

ZB


<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: 44px"><COL style="WIDTH: 31px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Part #</TD><TD>QTY</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>XX066</TD><TD style="TEXT-ALIGN: right">444</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>TH827</TD><TD style="TEXT-ALIGN: right">10</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

In my 2nd sheet, I have data that looks like this:

PLD


<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: 98px"><COL style="WIDTH: 74px"><COL style="WIDTH: 95px"><COL style="WIDTH: 60px"><COL style="WIDTH: 86px"></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></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Order Number</TD><TD style="FONT-WEIGHT: bold">LOB Name</TD><TD style="FONT-WEIGHT: bold">Demand Type</TD><TD style="FONT-WEIGHT: bold">Part QTY</TD><TD style="FONT-WEIGHT: bold">Part Number</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">18970</TD><TD>OPTIONS</TD><TD>In Destination</TD><TD style="TEXT-ALIGN: right">500</TD><TD>XX066</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">19231</TD><TD>OPTIONS</TD><TD>In Destination</TD><TD style="TEXT-ALIGN: right">180</TD><TD>XX066</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">19211</TD><TD>OPTIONS</TD><TD>In Destination</TD><TD style="TEXT-ALIGN: right">135</TD><TD>XX066</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">16454</TD><TD>OPTIONS</TD><TD>In Destination</TD><TD style="TEXT-ALIGN: right">120</TD><TD>XX066</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">19082</TD><TD>OPTIONS</TD><TD>In Destination</TD><TD style="TEXT-ALIGN: right">65</TD><TD>XX066</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">18138</TD><TD>OPTIONS</TD><TD>In Destination</TD><TD style="TEXT-ALIGN: right">8</TD><TD>TH827</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">17347</TD><TD>OPTIONS</TD><TD>In Destination</TD><TD style="TEXT-ALIGN: right">1</TD><TD>TH827</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">18310</TD><TD>OPTIONS</TD><TD>In Destination</TD><TD style="TEXT-ALIGN: right">1</TD><TD>TH827</TD></TR></TBODY></TABLE>



What I need is a formula that will sum the Part QTY in the 2nd sheet.

Desired Result:

ZB


<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: 44px"><COL style="WIDTH: 31px"><COL style="WIDTH: 108px"></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></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Part #</TD><TD>QTY</TD><TD>Sum Of Demand</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>XX066</TD><TD style="TEXT-ALIGN: right">444</TD><TD style="TEXT-ALIGN: right">1000</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>TH827</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">10</TD></TR></TBODY></TABLE>

In ZB sheet, there will only be 1 part per row, but in the PLD sheet, there could be many or no rows.

Not sure if I need to use a SUMPRODUCT, or SUMIF, or what... Any help would be appreciated.

 

[hiker95]

supdawg,

Something like this?

Excel Workbook
	A	B	C
1	Part #	QTY	Sum Of Demand
2	XX066	444	1000
3	TH827	10	10
ZB

 

[supdawg]

That looks good.. .Thank you. I just cant wrap my head around sum product.

 

[kelvin2088]

I believe you can also use SUMIF function

 

[bosco_yip]

1] Yes, the formula can use SUMIF instead of SUMPRODUCT, in the benefit of faster speed

=SUMIF(PLD!E:E,A2,PLD!D:D)

2] Regarding hiker95's formula :

=SUMPRODUCT(--(PLD!$E$2:$E$9=A2),--(PLD!$D$2:$D$9))

It is not necessary to use "--" in the 2nd array arguments , and should be look like :

=SUMPRODUCT(--(PLD!$E$2:$E$9=A2),PLD!$D$2:$D$9)

Regards
Bosco