Subtracting Quantity Sold twice from Inventory on Hand

[JCK101]

I volunteer for an Athletic Youth Club that needs to keep track of their inventory of basketball and soccer uniforms.

I'm not allowed to upload attachments, so here are the worksheets and headers with problem data.

INVENTORY sheet
Column Headings:

[TABLE="width: 564"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Date[/TD]
[TD="class: xl66, width: 64"]Product[/TD]
[TD="class: xl68, width: 64"]Size[/TD]
[TD="class: xl68, width: 64"]Jersey#[/TD]
[TD="class: xl68, width: 64"]Qty[/TD]
[TD="class: xl69, width: 105"]Helper[/TD]
[TD="class: xl67, width: 64"]Sold[/TD]
[TD="class: xl70, width: 64"]OnHand[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 564"]
<tbody>[TR]
[TD="class: xl68, width: 75, align: right"]11/2/2016[/TD]
[TD="class: xl69, width: 64"]Jersey[/TD]
[TD="class: xl70, width: 64"]AL[/TD]
[TD="class: xl70, width: 64"]38[/TD]
[TD="class: xl70, width: 64"]1[/TD]
[TD="class: xl72, width: 105"]Jersey-AL-38[/TD]
[TD="class: xl73, width: 64"]0[/TD]
[TD="class: xl74, width: 64"]1[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 564"]
<tbody>[TR]
[TD="align: right"]11/2/2016[/TD]
[TD]Shorts[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]Shorts-AL[/TD]
[TD]-2[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 564"]
<tbody>[TR]
[TD="class: xl69, width: 75, align: right"]11/3/2016[/TD]
[TD="class: xl70, width: 64"]Shorts[/TD]
[TD="class: xl71, width: 64"]AL[/TD]
[TD="class: xl71, width: 64"][/TD]
[TD="class: xl71, width: 64"]12[/TD]
[TD="class: xl72, width: 105"]Shorts-AL[/TD]
[TD="class: xl73, width: 64"]-2[/TD]
[TD="class: xl74, width: 64"]10[/TD]
[/TR]
</tbody>[/TABLE]

The shorts (Shorts) are identified by size only. The quantities can be from 0 to the number of shorts in stock.
The formula in Column(Sold) is: =-SUMIF(Sales!$G$2:$G$1285,F2,Sales!$H$2:$H$1285)
Note:
I use helper columns to identify unique jerseys and soccer sets, by size and jersey number, the quantities are always one (1).

Problem:
Shorts-AL were bought twice a total of 24. Then sold 2 of them, balance should be 22, but using above formula, it is 20, the -2 is deducted from both purchases

SALES sheet
All sales are registered here.
Column Headings:
[TABLE="width: 717"]
<tbody>[TR]
[TD="class: xl65, width: 75"]Date[/TD]
[TD="class: xl63, width: 64"]Receipt#[/TD]
[TD="class: xl63, width: 136"]Name[/TD]
[TD="class: xl64, width: 145"]Product[/TD]
[TD="class: xl64, width: 64"]Size[/TD]
[TD="class: xl64, width: 64"]JerseyNo[/TD]
[TD="width: 105"]Helper[/TD]
[TD="class: xl64, width: 64"]Qty[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 845"]
<tbody>[TR]
[TD="align: right"]1/19/2017[/TD]
[TD="align: right"]2998[/TD]
[TD]Erik Estrada[/TD]
[TD]Shorts[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD]Shorts-AL[/TD]
[TD]1[/TD]
[TD]$5.00[/TD]
[TD]$5.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/19/2017[/TD]
[TD="align: right"]2999[/TD]
[TD]Mathew Kingston[/TD]
[TD]SocSet[/TD]
[TD]YXL[/TD]
[TD]46[/TD]
[TD]SocSet-YXL-46[/TD]
[TD]1[/TD]
[TD]$10.00[/TD]
[TD]$10.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/19/2017[/TD]
[TD="align: right"]30201[/TD]
[TD]Harry Inchon[/TD]
[TD]Jersey[/TD]
[TD]AL[/TD]
[TD]8[/TD]
[TD]Jersey-AL-8[/TD]
[TD]1[/TD]
[TD]$10.00[/TD]
[TD]$10.00[/TD]
[/TR]
[TR]
[TD="align: right"]1/19/2017[/TD]
[TD="align: right"]30201[/TD]
[TD]Harry Inchon[/TD]
[TD]Shorts[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD]Shorts-AL[/TD]
[TD]1[/TD]
[TD]$5.00[/TD]
[TD]$5.00[/TD]
[/TR]
</tbody>[/TABLE]

I read in this forum that Access is better for tracking inventory, but could not find an appropriate template that keeps track of inventory by not only product type and size, but also jersey numbers on each uniform. Any help will be much appreciated. Have a great day!
JC

 

[Scott T]

Instead of including sales with purchase I would create its own record since the date matters.
[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DCE6F1"]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]1
[/TD]
[TD]Date
[/TD]
[TD]Product
[/TD]
[TD]Size
[/TD]
[TD]Jersey#
[/TD]
[TD]Qty
[/TD]
[TD]Helper
[/TD]
[TD]sold
[/TD]
[TD]OnHand
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]2
[/TD]
[TD]11/2/2016
[/TD]
[TD]Jersey
[/TD]
[TD]AL
[/TD]
[TD]38
[/TD]
[TD]1
[/TD]
[TD]Jersey-AL-38
[/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]3
[/TD]
[TD]11/2/16
[/TD]
[TD]Shorts
[/TD]
[TD]AL
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]Shorts-AL
[/TD]
[TD][/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]4
[/TD]
[TD]11/3/16
[/TD]
[TD]Shorts
[/TD]
[TD]AL
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]Shorts-AL
[/TD]
[TD][/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]5
[/TD]
[TD]11/19/17
[/TD]
[TD]Shorts
[/TD]
[TD]AL
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shorts-AL
[/TD]
[TD]-2
[/TD]
[TD]22
[/TD]
[/TR]
</tbody>[/TABLE]

=-SUMIFS(sales!$H$2:$H$5,sales!$G$2:$G$5,F2,sales!$A$2:$A$5,A2)

 

[JCK101]

Thank you Scott for taking the time to help. I plugged in your code and unfortunately it did not work.

The code I have now, works perfectly for sales of Jerseys and SocSet's, but not on generic products that don't have numbers on them also are bought in quantities and multiple times.

Also, the date in this case only, does not matter, it is informational for this operation.

The code I have, is looking up "all" rows with "Shorts-AL" and "subtracting" the qty from the Sales!H:H column corresponding to "Shorts-AL".

What I need to have is a code that will NOT subtract the qty from the Sales!H:H column from each row matching "Shorts-AL" in Inventory!G:G column, but DO subtract from only the first "Short-AL" that has in OnHand column a quantity equal to or more than the quantity sold. And if the quantity sold is "greater" than the quantity OnHand, subtract the balance from the "second" or Nth row matching "Shorts-AL".

Like I previously explained "Shorts-AL" were purchased twice, 12 each time, for a total of 24 so far, and only 2 have been sold, so the total OnHand should be 22, if 6 were sold the OnHand should be 18 and so forth.

Lets see if someone else can come up with a suggestion to make it work the way it should be.
JC

Instead of including sales with purchase I would create its own record since the date matters.

=-SUMIFS(sales!$H$2:$H$5,sales!$G$2:$G$5,F2,sales!$A$2:$A$5,A2)

 

[Scott T]

What did not work? Did you put the formula in G2 and copy down.

You could also just list all your products at the top and then use your sumif formula

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DCE6F1"]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]1[/TD]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]Size[/TD]
[TD]Jersey#[/TD]
[TD]Qty[/TD]
[TD]Helper[/TD]
[TD]sold[/TD]
[TD]OnHand[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shorts-AL[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]3[/TD]
[TD]11/2/2016[/TD]
[TD]Jersey[/TD]
[TD]AL[/TD]
[TD]38[/TD]
[TD]1[/TD]
[TD]Jersey-AL-38[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]4[/TD]
[TD]11/2/16[/TD]
[TD]Shorts[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]Shorts-AL[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]5[/TD]
[TD]11/3/16[/TD]
[TD]Shorts[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]Shorts-AL[/TD]
[TD][/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In G2 Do not copy it down. This will always so how many Shorts-AL you have sold and since you did not copy it will not be counted twice but could then be taken out of the on hand calculation.

[COLOR=#574123] =-SUMIF(Sales!$G$2:$G$1285,F2,Sales!$H$2:$H$1285)[/COLOR]

 

[JCK101]

Thank you Scott for your help.

Your solution cannot be used, I have to copy the formula down "all" rows not just the first row of the Shorts-AL, If I did what you are suggesting, my COGS and in stock values which I intend to implement will not be accurate.

Thank you for your effort.
Cheers.

 

[Scott T]

See if this works.

In G2 and copy down

=IF(SUMIF(F$1:F1,F2,H$1:H1)>0,0,IF((SUMIF(sales!$G$2:$G$1285,F2,sales!$H$2:$H$1285)-SUMIF(F$1:F1,F2,E1:E$1))<=(E2+SUMIF(F$1:F1,F2,E$1:E1)),-SUMIF(sales!$G$2:$G$1285,F2,sales!$H$2:$H$1285)+SUMIF(F$1:F1,F2,E$1:E1),-E2))

 

[JCK101]

[SOLVED] Re: Subtracting Quantity Sold twice from Inventory on Hand

Great job Scott,

It works perfect as far as I can test it. And works in FIFO method.

My next step is to add the COGS and test it further.

Can you describe what each section of the code is about, so I can understand it better and maybe use it in my COGS portion.

Again thank you for your help

 

[Scott T]

Re: [SOLVED] Re: Subtracting Quantity Sold twice from Inventory on Hand

This sums the on hand column with the range from the top to the row above. If his is greater then zero then this row should be 0 since there are still rows above with product on hand.

=IF(SUMIF(F$1:F1,F2,H$1:H1)>0,0,

The first part is your sumif formula then subtract the qty for all the rows above. This is compared to the total qty up to this row, so on the first shorts row it is 12 on the second on it is 24. This check if the amount sold will go over the Qty. If so it puts in the Qty as the number sold that is the -E2 at the end of the formula, otherwise it calculates the amount sold.

IF((SUMIF(sales!$G$2:$G$1285,F2,sales!$H$2:$H$1285)-SUMIF(F$1:F1,F2,E1:E$1))<=(E2+SUMIF(F$1:F1,F2,E$1:E1))

This calculates the amount sold by taking your sumif formula giving total sold as a negative number, and adding the total Qty not including this row, since that has already been sold.

-SUMIF(sales!$G$2:$G$1285,F2,sales!$H$2:$H$1285)+SUMIF(F$1:F1,F2,E$1:E1)