Hmm. Tried that but still seem to be getting the formula refers to empty cell error. Literally that formula you wrote points to D1, which as you can see in the excel document, is empty. Any ideas?


Quote Originally Posted by benishiryo View Post
your previous formula was looking at column F. now in your eg, i suppose you want to look at column C & count if there is 1 or more "Hello!". so try:
=IF((COUNTIF(INDIRECT("C"&3+((ROWS($D$1:D1)-1)*10)&":C"&12+((ROWS($D$1:D1)-1)*10)),"Hello!"))>=1,"Hello!","Not Hello!")

this is equivalent of putting:
=IF((COUNTIF(C3:C12,"Hello!"))>=1,"Hello!","Not Hello!")

but when dragged down, it increases the range by 10 rows. so it'll be:
=IF((COUNTIF(C13:C22,"Hello!"))>=1,"Hello!","Not Hello!")