If function problem - Please help

Hi, I created this if function for something im producing, for some reason its does not really work. Please help

{=IF(G4:AK6="S","",IF(G4:AK6="H","",IF(G4:AK6="F","",IF(G4:AK6="","","ERROR"))))}

It is searching a area for anything that is not the letter S, H or F (or a blank space). For some reason it only works if the invalid data is in cell G4 and only then do all of the if statments fail and the ERROR text is produced. Otherwise if invalid data is put anywhere else within that range notching happens.

I don't really know what im doing and I feel im using If statments in the wrong way.

A simple but less elegant solution would be to create a helper column with the following formula dragged down:

=IF(OR(A1="G",A1="F",A1="S",A1=""),"","Error")

And then replace your above formula with the following:

=IF(COUNTIF(B:B,"Error")=0,"","ERROR")

A better solution actually:

=IF(COUNTIFS(A:A,"<>S",A:A,"<>",A:A,"<>F",A:A,"<>H")=0,"","Error")

Just replace A:A with the range you are checking.

Amazing Thank you so much! Could you explain what it is doing?

Of course. The <> means not equal to, so it's looking down the column and counting every cell that's not equal to S, F, H or blank. If the figure is 0, it does nothing, but if it's anything else it returns "Error".