# Subtract IF multiple conditions

• I could use some help solving this problem. I have two sheets, one with orders the second with sales. I need help automatically subtracting from orders based on sales.

Order sheet: Column A is location, D is SKU, and F is Quantity.
Sold Sheet: Coulmn E is location, H is SKU, and I is Quantity.

Is it possible to have a script to run through every sale and if location and item are exactly the same, subtract quantity sold from the quantity ordered to give a new "due" quantity?

Worth noting, the order sheet is 18 months out for weekly dispatch. I need a way to limit the subtraction to only what is due before a certain date (column G in order sheet) There could be multiple sales to cover order in a given week.

I am essentially trying to find out what I still owe or am late on shipping. Sale is > Order, a negative number would work just fine.

Anything helps!

Thanks

• Re: Subtract IF multiple conditions

Your requirement as to 'due before a certain date' was somewhat vague, but possibly this untested procedure will work for you.

If I've been helpful, let me know. If I haven't, let me know that too.

• Re: Subtract IF multiple conditions

Thanks Dangelor,

With your script I kept running into an error here.
If vOrder(1, a) = vSold(5, b) And _
vOrder(4, a) = vSold(8, b) And _
vOrder(7, a) < Date - 540 Then 'not sure on this formula

Sorry for being vague, by the when I meant when the order was due. For example last week, we shipped for the order due 1/30 or before.

I have manually created a sheet in which I hope it will help.

Sold is all of the sold items from the week. Some items may not be on the actual order as customers call and ask for different items.
Order is what they have sent to me.
Results is what I hope to gain from this sheet. There is a column for ideal results, which means if over shipped this week, it would remove the overage from the next orders quantity but that is not required. A negative number in this weeks order would suffice. I need the sold to subtract the earliest possible when date. forum.ozgrid.com/index.php?attachment/71210/

## Files

• Re: Subtract IF multiple conditions

Sorry, but I don't quite see how you correlate your sales data to the results you display. I may be dim though. Hopefully someone else can help you. Good luck..

If I've been helpful, let me know. If I haven't, let me know that too.

• Re: Subtract IF multiple conditions

Hello Dangelor,

Essentially what I am trying to accomplish is if the plant and item number match, subtract the quantity sold from the quantity ordered. I also need a way to identify items sold not on an order, but that is very minor.

Any ideas are greatly appreciated.

• Re: Subtract IF multiple conditions

Perhaps this new file will help clarify what I am trying to achieve.

On the results tab everything highlighted in yellow is what would have changed. Items not highlighted in yellow there was no sale. The green and red formatting was for reference and is not required if to difficult. (would be ideal) column H, is illustrating, if this is even possible, once the order quantity reaches 0, for the script to keep searching sheet for next order with same criteria and subtract remain quantity. Anything will help, I am currently doing this manually over the weekend and generally 150+ sales per week and is very time consuming.
In simple terms, I am trying to have a script go through each sale row and subtract quantity from the order sheet if the plant and part number are the same. I will sort the order sheet based on when they are do so it removes from correct order.
Thanks for any help!

## Files

• Re: Subtract IF multiple conditions

I Thought I understood, but the example actually makes things more confusing

In your initial instructions you stated: Sold Sheet: Coulmn E is location, H is SKU, and I is Quantity.
In your example sold sheet: column E is blank, Column H is blank, and column I is blank AND outside the table. Nothing in the sold table appears to relate to the order table with the possible exception of "ship to Address 1"

In the results sheet, the first line had 13, now has -299, a difference of 312. The total for the "texas" lines in the sold sheet = 332. At that point I gave up.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!