Formula needed to determine net quantities of items, categorized by name and date

[jimithing007]

Hello,
Lower level Excel user here, hoping to get some guidance.

My company has to have a spreadsheet to track inventory usage for some components for a daily report. We need to track daily exactly how many of each item we receive into our warehouse and how many we consume to build orders. The final tab will be our "on-hand" quantity - the difference between received and consumed.

I have four tabs on this mock spreadsheet (screenshots linked below) -- "Receiving Report," "Consumption Report," "Consumption - TYPE Z items," and "Currently On Hand." The formula needs to display the result on the “Currently On Hand” tab under the corresponding date and by the corresponding “Supplier Part #” (Column A).

So we need a formula that will look at the “Receiving Report” tab, then take the “Qty Received” in Column D that corresponds with “Supplier Part #” in Column C, by the “Received Date” in Column E. Then it needs to look at the “Consumption Report” tab to determine the total number of each item consumed in a given day. Each item could be listed more than one time in a day. Each item could also be listed more than once a day on the “Receiving Report” too, as we have to keep track of each PO# for which they arrive. So the formula will need to be able to accommodate that.

The difference between the Receiving Report and the Consumption Report should display on the “Currently on Hand” tab by the corresponding date.

For the five items labeled “TYPE Z” at the end – those quantities received will still be reported in the Receiving Report tab, but the consumption quantities have to be tracked separately, hence the “Shipments – TYPE Z items” tab. Otherwise, this is handled the same way, and the result should display the same on the “Currently On Hand” tab.

Any help is appreciated. Please let me know if any clarification is needed. I'm using Excel 2016. Here's some screenshots of each of the four tabs on the report, if this helps:

https://imgur.com/a/dGaGO

 

[Scott T]

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
1	supplier part #	11/15/2017	11/16/2017	11/17/2017	11/18/2017	11/19/2017	11/20/2017	11/21/2017	11/22/2017	11/23/2017	11/24/2017	11/25/2017
2	keyboard	15	13	13	13	13	13	13	13	13	13	13
3	mechanical keyboard	10	7	2	2	2	2	2	2	2	2	2
4	17-inch monitor	11	11	10	8	8	8	8	8	8	8	8
5	21-inch monitor	7	7	5	0	0	0	0	0	0	0	0
6	keyboard type Z	200	200	180	180	30	30	30	30	30	30	30
7	Speakers Type Z	200	200	180	180	30	30	30	30	30	30	30
8	Video Card	0	6	6	6	6	6	6	6	6	6	6

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Currently On Hand​

Worksheet Formulas Cell Formula B2 =SUMPRODUCT('Receiving Report'!$D$2:$D$11*('Currently On Hand'!$A2='Receiving Report'!$C$2:$C$11)*('Currently On Hand'!B$1>='Receiving Report'!$E$2:$E$11))-SUMPRODUCT('Consumption Report'!$E$2:$E$11*('Currently On Hand'!$A2='Consumption Report'!$D$2:$D$11)*('Currently On Hand'!B$1>='Consumption Report'!$A$2:$A$11))-SUMPRODUCT('Consumption - TYPE Z items'!$D$2:$D$11*($A2='Consumption - TYPE Z items'!$C$2:$C$11)*('Currently On Hand'!B$1>='Consumption - TYPE Z items'!$A$2:$A$11)) <tbody> </tbody>

<tbody>
</tbody>

Adjust ranges as need and copy formula down and across.

 

[jimithing007]

Hi Scott,

Thanks for your assistance.

I pasted that formula in directly and got a #VALUE ! error. Then, I changed the values to reflect where the data begins (there are two headers above each table so I was thinking that's where the error was coming from). So now the formula looks like this:

=SUMPRODUCT('Receiving Report'!$D$3:$D$800*('Currently On Hand'!$A2='Receiving Report'!$C$3:$C$800)*('Currently On Hand'!B$4>='Receiving Report'!$E$3:$E$800))-SUMPRODUCT('Consumption Report'!$E$3:$E$800*('Currently On Hand'!$A4='Consumption Report'!$D$3:$D$800)*('Currently On Hand'!B$4>='Consumption Report'!$A$3:$A$800))-SUMPRODUCT('Consumption - TYPE Z items'!$D$3:$D$800*($A3='Consumption - TYPE Z items'!$C$3:$C$800)*('Currently On Hand'!B$4>='Consumption - TYPE Z items'!$A$3:$A$800))

That gives me this error: "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells."

When I click "OK" I just get a value of 0.

What did I mess up?

Thanks again.

 

[Scott T]

Either the formula is pointed to the cell that the formula is in or it is referring to a cell that uses the cell were the formula is.

For example this is a circular reference since it is summing A1:A3 and the sum formula is in A3.
Excel 2010

	A
1	1
2	2
3

<tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula A3 =SUM(A1:A3) <tbody> </tbody>

<tbody>
</tbody>

This is also a circular reference since A2 is = to A3 and A3 is the sum of A1:A2
Excel 2010

	A
1	1
2	0
3	3

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula A2 =A3 A3 =SUM(A1:A2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Check your formulas to see what cells they are referencing and you should find your problem.

 

[jimithing007]

In your formula, is the bolded area below to pull the date field from the "Currently on hand" tab? If so, I think I found the circular reference.

=SUMPRODUCT('Receiving Report'!$D$3:$D$800*('Currently On Hand'!$A2='Receiving Report'!$C$3:$C$800)*('Currently On Hand'!B$2>='Receiving Report'!$E$3:$E$800))-SUMPRODUCT('Consumption Report'!$E$3:$E$800*('Currently On Hand'!$A4='Consumption Report'!$D$3:$D$800)*('Currently On Hand'!B$2>='Consumption Report'!$A$3:$A$800))-SUMPRODUCT('Consumption - TYPE Z items'!$D$3:$D$800*($A3='Consumption - TYPE Z items'!$C$3:$C$800)*('Currently On Hand'!B$2>='Consumption - TYPE Z items'!$A$3:$A$800))

I had changed that to !B$4, which is the cell itself that I was pasting into. Changing that to !B$2 is still giving me the wrong number though. I get -7 for "Keyboard." There's got to be something else I'm missing too.

Adding more complexity - there's another issue that I hadn't considered. This formula will subtract the consumption quantity from the received quantity, correct? I hadn't considered that we'd have leftover quantities from the previous day, so we'd have to have a formula takes that into account too.

Let's say 11/10 is the first day we start this report. I received in 10 keyboards on 11/10, and consumed 5 keyboards on 11/10 - then my on-hand on 11/10 should be 5.

I receive in 3 keyboards on 11/11, and consume 0 - my on-hand quantity should be 8 because I have five leftover from 11/10.

Am I making sense?

 

[Scott T]

The bolded part should point to the date on the currently on hand sheet. I post 2 it points to 'Currently On Hand'!B$1 with B1 = to 11/15/17.

The formula I posted takes into account leftover quantities by not looking for date1 = date2 but by looking if the date1 is >= to date2. This will pull all past received and past used for the item. So on 11/11 it will add the 10 keyboards from 11/10 and the 3 from 11/11 then subtract the 5 from 11/10 to give 8.

 

[jimithing007]

It works!

I had a couple of cell ranges incorrect. Fixed those, now it works like a charm.

You rule Scott! Thanks so much.