Solved I Need To Sum Values Based On Different Conditions

April 7, 2013 at 04:46:10
Specs: Windows 7
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 - TOTAL

First 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).


See More: I Need To Sum Values Based On Different Conditions

Report •


✔ Best Answer
April 7, 2013 at 08:20:30
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.



#1
April 7, 2013 at 06:18:24
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.


Report •

#2
April 7, 2013 at 06:30:45
Please click on the blue line at 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.


Report •

#3
April 7, 2013 at 07:04:54
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	-£30

STOCK 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.


Report •

Related Solutions

#4
April 7, 2013 at 08:20:30
✔ 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.


Report •

#5
April 7, 2013 at 08:35:43
Hi sorry for the confusion, your explanation has helped to do what I needed to do and I was complicating it myself. Thanks

Report •

Ask Question