IF AND OR Statement

[Mike7792]

Hello,

I'm sure it's relatively simple but I'm trying to do a formula whereby I can return the Ref no. in column C if there is cost in column A but no volume in Column B or vice versa. I want to try and do it all in one formula but when i try and add in the OR part of the statement, excel wants me to multiply by OR which makes it error. Basically if there are any instances where there are cost with no volume or volume with no cost, I'd like to return the reference in column C

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cost[/TD]
[TD]Volume[/TD]
[TD]Ref[/TD]
[/TR]
[TR]
[TD]£200[/TD]
[TD]2[/TD]
[TD]AL001[/TD]
[/TR]
[TR]
[TD]£100[/TD]
[TD]4[/TD]
[TD]AL002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD]AL003[/TD]
[/TR]
[TR]
[TD]£400[/TD]
[TD]6[/TD]
[TD]AL004[/TD]
[/TR]
[TR]
[TD]£180[/TD]
[TD][/TD]
[TD]AL005[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated.

Thanks

 

[JustynaMK]

Hi Mike,

If your Cost is in column A (formatted as "Currency") and Volume is in column B (formatted as "Number") then maybe try to do the following:
=IF(AND(A1*B1=0,SUM(A1:B1)<>0),"display ref number","do not display ref number")

Not sure how you want to display the Reference number - is it a lookup to another column?

Let me know if that works for you.

Best regards,
Justyna

 

[Mike7792]

Hi Justyna,

Yes this works perfectly, thank you for taking the time to reply.

Have a great day

Mike

 

[JustynaMK]

Cool, glad to hear it works!

Take care,
Justyna

 

[jtakw]

Hi,

If Columns A and B are Numbers, use D2 formula copied down.
If Columns A or B might be Text, then use E2 formula copied down.

Book1
	A	B	C	D	E
1	Cost	Volume	Ref
2	$200	2	AL001
3	$100	4	AL002
4		5	AL003	AL003	AL003
5	$400	6	AL004
6	$180		AL005	AL005	AL005
Sheet165

Cell Formulas
Range	Formula
D2	=IF(COUNT(A2:B2)=2,"",C2)
E2	=IF(COUNTA(A2:B2)=2,"",C2)