Calculates Qty Received

[Andri]

Dear All,
Please find the first sheet as follows:
PO# Part Number Qty Purchased Qty
Received
4734LA004 3-1439-6 6
by formula?

While the second sheet as follows:
PO# Part Number Qty Received
Date of Received
4734LA004 3-1439-6 1
1Mar05
4734LA004 3-1439-6 2
10Mar05
4734LA004 3-1439-6 1
1 April05

how is the right coding for formula : to get the result in Sheet 1, column
Qty Received to reflect 4ea. Has tried Sum, frequency, index, and match
function, still not able to solve...
the Match Criteria: that PO# AND ("&") Part Number is must be equal between
sheet 1 and sheet 2.

Need your help, plz

Brgds,andri

 

[Bob Phillips]

=INDEX(Sheet2!C2:C00,MATCH(A2&B2,SHeet2!A2:A100&Sheet2!B2:B100,0))

which is an array formula, so commit with Ctrl-Shift-Enter




RP
(remove nothere from the email address if mailing direct)

 

[Andri]

Dear Bob,
The result of the formula still 1ea instead of 4ea.
due to that formula only find the first ROW(second parameter in index
formula) Sheet2!C2:C100.
Please advice...

brgds,andri

 

[Bob Phillips]

Sorry, I don't understand. Where does 1ea and 4ea figure in all this?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Andri]

Dear Bob,
Please notice in sheet2, there are three times of receiving period of the
parts purchased.

So this is the scenario, we bought 6ea (Quantity Ordered), and have received
3 times (1ea, 2ea, 1ea) in different RECEIVED date.

So the purpose to know, there is BACK ORDER of 2ea.

Thank you

 

[LanceB]

I think your looking for in the qty purchased column

Sumproduct((sheet2_range of part numbers=sheet1partnumber)*
(sheet2_range of dates=sheet1date)*(sheet2_range of quantity))

Exampl
=SUMPRODUCT(--(Sheet2!A1:A6=Sheet1!A14)*--(Sheet2!B1:B6=Sheet1!B14)*(Sheet2!C1:C6))

Note: all ranges must be of the same length
a1:a6 b1:b6 etc

LAnce

 

[Andri]

Dear Lance,
I dont think i would like to calculate by formula "Sumproduct".
The purpose to know back order (Qty Purchase - Oty Received) in this case
6ea - 4ea, so back order 2ea.

let we say, we buy certain item, with PO# and Part Number with quantity 6ea.
it reflected in sheet1.
We have received partial shipment of that order. There are 3 shipment with
the following qty 1ea + 2ea + 1ea = 4ea.It reflected in sheet2.

I would like to get the RECEIVED QTY by MATCH the PO# AND PartNUMBER in
sheet1 and sheet2.

Thank you and need you further advice

 

[LanceB]

Andri
It is difficult to be sure what you are looking at, but sumproduct is one
of the most powerful functions in Excel. It allows you to count or sum items
with multiple criteria. It is used to look thru a list of data and return as
an example the quanity of all products of a unique number recieved on a
certain date from a list of multiple products and dates. If you need to
compare that to products sold you could simply subtract that result from a
sumproduct function returning the the sold products.

You don't need to respond to this if I'm missing your point, just wanted to
make sure you were not overlooking this feature.

 

[Andri]

Dear Lance,
Thank you for the advice. Yes you are right, i have never use this function
before.
So will follow your advice and i will try to study it in details.

Thank you.
Andri

 

[Andri]

Dear Lance,
Thank you very much. It is an amazing formula to solve my problem. And
definitely your advice is highly appreciated.
Case Closed....

 

[LanceB]

Thanks for the feedback
Lance

Dear Lance,
Thank you very much. It is an amazing formula to solve my problem. And
definitely your advice is highly appreciated.
Case Closed....