I think I should use IF but can't seem to get my head round it as I think it should be IFSUM Column A - 'SUPPLIER','DF','KB','KG'

Column B - 'W/H','SR-*'

Column C - QTY

Column D - INDIVIDUAL ITEM PRICE

Column E - TOTALFirst step is am not sure how to get it to recognise the start of our job numbers which is the SR- , they are all SR- followed by 5 numbers.

Second step is that I need it to ask questions of columns A & B to determine whether the value in column E should be negative or positive.

The reason for this is that I am trying to have a running value of stock but our parts aren't fixed prices so from one week to the next the prices would be diferent.

So I am thinking:

If column A = Supplier & Column B = W/H then Column E would be positive the value calculated in column C & D (C*D)

OR

If column A = DF & Column B = SR- then Column E would be negative the value calculated in column C & D (C*D).

✔ Best Answer

I'm still confused. You said:

Column E needs to add the IN's from SUPPLIER and minus the OUT's to SR-. The IN's (DF,KB,KG) are internal warehouse transfers from the main W/H so not relevant.When I look at your table, SUPPLIER is under the OUT heading and the SR- entries are under the IN heading. Isn't that the opposite of what you said in your text?

In any case, I can get the values you have in Column E with this formula but I'm not sure if it is what you are looking for.

=IF(B2="W/H",C2*D2,IF(OR(B2="DF",B2="KB",B2="KG"),0,C2*D2*-1))

You didn't use Row numbers in your example, so I am assuming your column headers are in Row 1. If I put that formula in E2 and drag it down, E2:E8 will Sum to £40.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

When posting in a help forum such as this, please try to use a subject line that is relevant to the contents of your post. If everyone used a generic subject line like "I don't know what formula to use" we wouldn't be able to tell one question from the next. I have edited your subject line to be more descriptive of the question you have asked.

DerbyDad03

Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Please click on the blue lineat the end of this post and read the instructions on how to post example data in this forum.Then please post some example data and the desired result based on that data so that we can better understand your question.

At this point I don't understand what you are trying to do and how/why W/H and SR relate to negative/positive values.

Keep in mind that we can't see your spreadsheet from where we're sitting. Examples can go a long way to helping us understand your needs.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

A B C D E OUT IN QTY VALUE SUBTOTAL SUPPLIER W/H 10 £10 £100 W/H DF 1 £10 £0 W/H KB 2 £10 £0 W/H KG 3 £10 £0 DF SR-11111 1 £10 -£10 KB SR-11112 2 £10 -£20 KG SR-11113 3 £10 -£30STOCK VALUE: £40

The stock value needs to calculate the value of stock in and out, so Column E needs to add the IN's from SUPPLIER and minus the OUT's to SR-. The IN's (DF,KB,KG) are internal warehouse transfers from the main W/H so not relevant. The product value's change each day or week so are marked up with the price so this would be entered by whoever is updating the spreadsheet.

I'm still confused. You said:

Column E needs to add the IN's from SUPPLIER and minus the OUT's to SR-. The IN's (DF,KB,KG) are internal warehouse transfers from the main W/H so not relevant.When I look at your table, SUPPLIER is under the OUT heading and the SR- entries are under the IN heading. Isn't that the opposite of what you said in your text?

In any case, I can get the values you have in Column E with this formula but I'm not sure if it is what you are looking for.

=IF(B2="W/H",C2*D2,IF(OR(B2="DF",B2="KB",B2="KG"),0,C2*D2*-1))

You didn't use Row numbers in your example, so I am assuming your column headers are in Row 1. If I put that formula in E2 and drag it down, E2:E8 will Sum to £40.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi sorry for the confusion, your explanation has helped to do what I needed to do and I was complicating it myself. Thanks

Ask Your Question

Weekly Poll