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.

Size	Quality	order supplier	order customer	Stock
5X5	High	200	150	50
5X5	High	50	50	50
6X9	Low	100	25	75
5X5	High	0	50	0

<tbody>
</tbody>

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:

Size	Quality	order supplier	order customer	Stock
5X5	High	200	150	0
5X5	High	50	50	0
6X9	Low	100	25	0
5X5	High	0	50	0
5X5	High	0	50	-50
6X9	Low	25	0	100

<tbody>
</tbody>

 

[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