So I'm struggling with the following:

I have this formula (I know it's huge but it works so far)

``````=IF(AND(COUNTIF(basictier1;"*"&A4&"*"));"T1 Basic"; IF (AND(COUNTIF(basictier2;"*"&A4&"*"));"T2 Basic";IF(AND(COUNTIF(basictier3;"*"&A4&"*"));"T3 Basic";IF(AND(COUNTIF(basictier4;"*"&A4&"*"));"T4 Basic";IF(AND(COUNTIF(basictier5;"*"&A4&"*"));"T5Basic";IF(AND(COUNTIF(basictier6;"*"&A4&"*"));"T6 Basic"))))))
``````

The basictier1, 2 etc are all ranges. The T1 Basic etc are values the K column has to get when something from the Basictier1 range has been selected in the A column. So that all works, but...I only want to have the value per cell in column K empty instead of always displaying T1 Basic when the cells in column A are empty.

Can anyone give me a tip? I feel that it's not that complicated in Google Spreadsheet, although many things seem to be unfortunately.

• Can you share a google spreadsheet with us? It makes this apparent not complicated question a bit less complicated ;-) – Jacob Jan Tuinstra Nov 27 '12 at 17:37

Try this out:

``````=IF(A4="",K4="",IF(AND(COUNTIF(basictier1;"*"&A4&"*"));"T1 Basic"; IF (AND(COUNTIF(basictier2;"*"&A4&"*"));"T2 Basic";IF(AND(COUNTIF(basictier3;"*"&A4&"*"));"T3 Basic";IF(AND(COUNTIF(basictier4;"*"&A4&"*"));"T4 Basic";IF(AND(COUNTIF(basictier5;"*"&A4&"*"));"T5Basic";IF(AND(COUNTIF(basictier6;"*"&A4&"*"));"T6 Basic")))))))
``````

I have just added one if condition to your formula, that is

``````=IF(A4="",K4="",Else Formula provided by you)
``````

above formula validates your required contion.