adapting a formular

[DarlowG]

Hi

[TABLE="width: 500"]
<tbody>[TR]
[TD]Apples [/TD]
[TD]Bananas[/TD]
[TD]Shopping [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Say i want to put a formula in the shopping column that will put a 1 in the shopping column if there is no figure in Apples and a figure in Bananas.
I also want a want to leave it blank if there is any figures in Apples columns or if there is a number in the apples and banana's column

i have tried the if formula


=IF(SUM(A1+SUM(B1)<>0,1,0)

it doesn't work

Can anyone help I've been trying to work on this for an hour and its doing my brain in

Cheers

Gen

 

[Michael M]

Maybe

=IF(AND(B2<>"",A2<>""),"",IF(A2="",B2,""))

 

[DarlowG]

sorry, im trying to understand

where do i put the 1 in the formular that will indicate that A1 is blank but not B1 ?

 

[mick0005]

Maybe this? Make sure you try this with several more examples of your data to be sure its working correctly. If none of your criteria are met I have it outputting "error". Obviously you can change that to whatever you want.

Excel 2010

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Apples[/TD]
[TD="align: center"]Bananas[/TD]
[TD="align: center"]Shopping[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Error[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(AND(A2="",B2<>""),"1",IF(A2<>"","",IF(AND(A2<>"",B2<>""),"","Error")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]