Trouble with 'Quantity Delivered'

Shubham

I am working on Inventory Management Project, and I am facing one issue there, I have one “Data sheet”, here I am maintaining the data location-wise, and maintaining the information like utilization, Quantity manufactured, and Quantity delivered, and I am facing an issue with “Quantity delivered”.

Location combinations like:

Note: This screenshot is only for reference.

For row number 1-4 combination is the same and utilization is 25% for each row.

For row number 5 & 6 combination is the same and utilization is 50% for each row.

I have also one “Order sheet”, where I am maintaining all the orders which I received.

I have also one “Fulfilment sheet” where I am maintaining all the fulfilment details. When I am submitting the information for fulfilment for a particular location like 98765 + 1 + 1 + 1 and Quantity delivered is 50 for this location, it is calculated for all the same locations in the “Data sheet”.

Screenshot of “Fulfilment sheet”.

Below is the screenshot of “Data sheet” for reference, so as per the example I have ordered 50 qty for this 98765 + 1 + 1 +1 combination so it is showing 50 qty delivered in all the 4 rows (1-4) so per the requirement, it should subtract from 1st row but for now it subtracting from each row.

To get the Quantity delivered from the Fulfilment sheet to Datasheet, I am using the SUMIF formula, and I have created unique key combinations with “Location, A, B, and C”.

 When I submitted the details for fulfilment only for 98765 + 1 + 1 + 1 location, then the data sheet should not be calculating the data from all the locations for the same combination.

I would like it to calculate for the first row, including the following information:

For example:

If I will submit the fulfilment quantity of 120.

Screenshot of “Fulfilment sheet”.

so, it should calculate like the below screenshot:

The calculation should be for row 1st Quantity delivered 100 & Available quantity 0 and for row 2nd Quantity delivered 20 & Available quantity 80.

 

Thanks in advance.

Genevieve P.

Hi @Shubham

Your formula will be unable to do this because your "unique identifier" is the same across rows 1 - 4. This is why you're seeing the 50 subtracted from each of those 4 rows - the formula can't tell which one you want it to work on.

You would need to have a summary with the total for each Location+A+B+C that your formula can use as a reference, instead of having the same Location+A+B+C with multiple duplicates, does this make sense?

What I would do is set up a column that's called something like "Total Manufactured for this Location". Then use a SUMIF in this current sheet to SUM together all the values in your Quantity Manufactured column for that Location+A+B+C

For example:

=SUMIFS([Quantity Manufactured]:[Quantity Manufactured], Location:Location, Location@row, A:A, A@row, B:B, B@row, C:C, C@row)

This would output 400 for every row of 98765+1+1+1

Then you can have another column which is the Total Quantity delivered for Location+A+B+C in the other sheet. This would be your SUMIFS but across sheets:

=SUMIFS({Fulfilment Sheet Qty Delivered}, {Location}, Location@row, {A Column}, A@row, {B Column}, B@row, {C Column}, C@row)

Now you can subtract this from your Total Manufactured to get an available quantity. Does that make sense?

Cheers,

Genevieve