IF statement

Solved/Closed
TOBES - Dec 2, 2009 at 06:12 AM
 TOBES - Dec 5, 2009 at 06:27 AM
Hello,
Please help me with the following if statement :
=IF(A1:A34<>"Ba","Ba","")

Column A1 to A34 contains the following text :Co, Cs, Th, Nk etc. If Ba is not listed in the column, I want Ba to be printed at the bottom of the range A1:A34 else blank. However all I get is##### !!! What's wrong ? Please help !

1 reply

Hi Tobes,

You are using an array in your formula thus you need to enter it as an array formula.
An array formula looks a little different then the one you use.

I don't think that what you want can be done with a single formula.
So first make an array formula in cell A36:
=SUM(IF(A1:A34="Ba",1,0)) confirm by hitting Ctrl+Shift+Enter (making it an array formula).
This will count how many times Ba is used in range A1:A34.
Now put an IF formula in A35 (bottom of list):
=IF(A36=0,"Ba","")

NOTE that you can either hide row 36 or make the textcolor white if you don't wish to see the result of the formula in A36.

Best regards,
Trowa
0
Hi Trowa

Thanks for the solution. I've battling with this for a while. I understand what you're doing here to solve it.
Much appreciated. Thanks.

Cheers
Tobes.
0