If cell contain "xy" multiply by

[carrriee90]

Hello everyone, I really need help with a formula!

Invoiced by	Company	Amount

<tbody>
[TD="width: 107"]Lead[/TD]
[TD="width: 114"]To XY[/TD]
[TD="width: 141"]To AB[/TD]

[TD="class: xl63"]XY[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]600[/TD]
[TD="class: xl63"]XY[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]AB[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]2560[/TD]
[TD="class: xl63"]AB[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]XY[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]1000[/TD]
[TD="class: xl63"]AB[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63"]AB[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]1500[/TD]
[TD="class: xl63"]XY[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

</tbody>

I need to create a formula whch says that

• if money is invoiced by XY and is is the lead of XY the amount must be multiplied by 0.2 in column To AB
• if money is invoiced by AB and is the lead of AB the amount must be multiplied by 0.2 in column TO XY
• if money is invoiced by XY and the lead of AB the amount must be multiplied by 0.3 in column To AB
• if money is invoiced by AB and the lead of XY the amount must be multiplied by 0.3 in column To XY

Is it possible to put this in one function?

Thanks in advance!!!

 

[hurgadion]

Hi,
let Your Data be in A1:F5, in Column E (with the Text To XY) I propose a Formula:

=IF((RIGHT($E$1,2)<>A2)*(RIGHT($E$1,2)<>D2),0,2*C2,IF((RIGHT($E$1,2)<>A2)*(RIGHT($E$1,2)=D2),0,3*C2,""))

Try to create a suitable Formula in Column F, this is Your HomeWork, best regards.

 

[Andrew Poulsom]

Welcome to MrExcel.

Here is a single formula solution:

=IF(AND($A2="XY",RIGHT(E$1,2)="AB"),$C2*IF($D2="XY",0.2,0.3),IF(AND($A2="AB",RIGHT(E$1,2)="XY"),$C2*IF($D2="AB",0.2,0.3),""))

 

[Marcol]

Try this in E2, Drag Across to F2 then Down

Excel Workbook
	A	B	C	D	E	F
1	Invoiced by	Company	Amount	Lead	To XY	To AB
2	XY		600	XY	180
3	AB		2560	AB		512
4	XY		1000	AB	200
5	AB		1500	XY		450
Sheet1

 

[carrriee90]

Thanks for the answers but I keep on getting a message that the formulas contain errors. No idea what i am doing wrong!

 

[Marcol]

What delimters does your system use?
Try changing the commas to semi-colons.