IF statement

Solved/Closed
Report
-
 TOBES -
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
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.