Excel Challenge 4

[Jalal_1988]

Hi All

I have problem in applying formula in Excel as following:
I Have some items with specific purchase code for each purchase times, also I have purchase Quantity and selling quantity, I need Stock quantity for each items per year in our warehouse,
Fro example for item A we have purchase 200 PC in 2013 and sold 100 PC in this year so in the end of year we have 100 PC in our stock,
IN 2014 we have bought another 250 PC so we had 350 PC in stock , and sold 190 PC n this year, so we had 60 PC in our stock,
So I need stock fir each item in end of years:
I attached the excel file which do it manually for stock and repeated in its columns:

I appreciate for your help in advance, 

[#2]

@Jalal_1988 

Could you please clarify

for first row you sell 10, purchase 200, how 100 appears in last columns?

in next row you sell 20 (total 10+20=30), purchase another 200 (total 200+200=400) and still have 100 in the last column.

Or that's only one purchase per each year?

[#3]

@Sergei Baklan 
Dear Sergei 
 
For each Cod the Purchase quantity is repeated , For each code only we purchase one time, 

sincerely 

[#4]

@Jalal_1988   I think I see what you want even though I still don't understand why you do it that way.  But even so I don't understand why the total at the end of 2014 is 60 instead of 0 since you show 2 buys of 50 each.  In any case try this formula:

=[purchase amt] - SUMIF( [year column], "<=" & [year cell], [purchase column])

so if your sample table is in the upper left corner then the formula in  G2 would look like:

EDIT: I retract the previous formula because although I think it is inline with what you want, I didn't see the whole sheet and all the inconsistencies that are in it as @Sergei Baklan mentioned just a few.  

[#5]

@Jalal_1988 

Still can't understand the logic. Let take code 102

We calculate Item A and Item B separately for this code or together. If separately, Item A purchased 200 in 2013, sold 100 in 2013 (rest is 100, as is in the sample) and another 100 in 2014. Thus it shall be zero on the end of 2014 since there were no purchases of this code.

If calculate items together also not clear what is behind the numbers in last column.

[#6]

@Sergei Baklan 
Dear Sergei
We need Stock in en of each Selling year, (forget purchase year),
1- Purchase Q is repeated for each code, 
2- For item A, we Purchase 200 PC in 2013 and sold 100 PC in 2013 so end of selling year 2013 we have 100 PC as stock, 
3- For item A in 2014 we Purchase another 250 Pc so we have 350 PC, and sum of sold Q is 190 Pc so we have 60 Pc in end of selling  year 2014 in stock
for other items is like A
(just consider Selling year for Stock)  

[#7]

@Jalal_1988 

Thank you. So, we don't care about the codes, we only calculate items, correct?

[#8 · Accepted solution]

@Jalal_1988 

I'd add helper column

(here is K) to indicate one-time purchase for each item in each year. Formula is

=--(COUNTIFS($G$4:$G4,G4,$D$4:D4,D4)=1)

End of the year balance could be calculated as

=SUMIFS($F$4:$F$23,$D$4:$D$23,D4,$G$4:$G$23,"<="&H4,$K$4:$K$23,1)-
SUMIFS($E$4:$E$23,$D$4:$D$23,D4,$H$4:$H$23,"<="&H4)

In you previous post

3- For item A in 2014 we Purchase another 250 Pc so we have 350 PC, and sum of sold Q is 190 Pc so we have 60 Pc in end of selling  year 2014 in stock

Actually 350-190 is equal to 160, not 60