Substract / add ONLY after checking several colums if its the same

[Ignore]

I'm currently making an excel file for someone. But I'm quite stuck.
Here's the problem.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Size[/TD]
[TD]Quality[/TD]
[TD]order supplier[/TD]
[TD]order customer[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]5X5[/TD]
[TD]High[/TD]
[TD]200[/TD]
[TD]150[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]5X5[/TD]
[TD]High[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]6X9[/TD]
[TD]Low[/TD]
[TD]100[/TD]
[TD]25[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]5X5[/TD]
[TD]High[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

I can't find the formula for stock. It MUST check size and quality before being able to substract / add. The column Stock must know wh ere to substract. Only if quality and size is the same.

 

[Canapone]

Hi

in E2

=SUMIFS(C$2:C2,A$2:A2,A2,B$2:B2,B2)-SUMIFS(D$2:D2,A$2:A2,A2,B$2:B2,B2)

or Excel 2003 or previous

=SUMPRODUCT(C$2:C2,--(A$2:A2=A2),--(B$2:B2=B2))-SUMPRODUCT(D$2:D2,--(A$2:A2=A2),--(B$2:B2=B2))

Hope it helps

 

[Ignore]

Is it possible for the whole excel file. From top to bottom?

Edit: it's not working for me

 

[Ignore]

I think I got it working. THanks man!

 

[Canapone]

Ciao,

you're welcome: thanks for your kind feedback.

 

[Ignore]

I Pmed you the same

Thanks for you help! I appreciated it very much!

If a new order appears. the stock goes to 0 and only the last one will show a number?

for example:



[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Size[/TD]
[TD]Quality[/TD]
[TD]order supplier[/TD]
[TD]order customer[/TD]
[TD]Stock[/TD]
[/TR]
[TR]
[TD]5X5[/TD]
[TD]High[/TD]
[TD]200[/TD]
[TD]150[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5X5[/TD]
[TD]High[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6X9[/TD]
[TD]Low[/TD]
[TD]100[/TD]
[TD]25[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5X5[/TD]
[TD]High[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]0

[/TD]
[/TR]
[TR]
[TD]5X5[/TD]
[TD]High[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]-50[/TD]
[/TR]
[TR]
[TD]6X9[/TD]
[TD]Low[/TD]
[TD]25[/TD]
[TD]0[/TD]
[TD]100

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Canapone]

Hi,

not sure to have understood. In you example the stock related to 5x5 high should be

50 (E2) 50(E3) 0 (E5) -50 (E6)

-50 because you're out of stock.

What would you need to get?

Here a little sample you can download

https://dl.dropboxusercontent.com/u/106022761/DROPMREXCEL.xlsx

Regards

 

[Ignore]

Thanks
What I mean. Is it possible that only the last one shows how much there's in stock.
Now with 5x5 you can se there are 50 too few but in the rows above you can see 0, 50 and 50. Is it possible that the rows above becomes '0'.
So people can only see the LAST stock?

 

[Canapone]

Hi, thanks for the feedback

In E2 you could try

=(SUMIFS(C$2:C$1000,$A$2:$A$1000,A2,$B$2:$B$1000,B2)-SUMIFS($D$2:$D$1000,$A$2:$A$1000,A2,$B$2:$B$1000,B2))*(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2))

Hope that helps

 

[Ignore]

Gonna test it out Gonna let you know if it works :D


Thank you so much