Take a look at the INDIRECT function, for your example it would be something like =COUNTIF(INDIRECT("A1:" & somecalculationthatreturnsa4asstring),"S")