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" wrote:
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" wrote:
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" wrote:
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))
Example
=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" wrote:
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
|