спасибо всем за помощь, но вот именно то, что я имела ввиду и нужно было, додумалась наконец-то:  
 
=COUNTIF(INDIRECT(ADDRESS(1,COLUMN(F1)-F2,4)):INDIRECT(ADDRESS(1,COLUMN(F1)-1,4)), "new")  
 
теперь всего лишь меняя число в F2, меняется и начальный диапазон.

Нужно выделить диапозон ячеек ссылаясь на др.ячейку. Допустим формула находится в F1:  
=COUNTIF(C1:E1, "new")  
то есть в ней range of cells - предыдущие 3 ячейки слева от F1. Но мне нужно чтобы диапозон был нефиксированным. Чтобы он мог ссылаться допустим на F2, в которой будет цифра - к примеру если 3, то диапазон C1:E1, а если 4, то уже B1:E1 (будет брать 4 предыдущие ячейки), если 5, то диапозон будет A1:E1. То есть изменяя цифру в F2, диапозон в формуле F1 будет автоматически изменяться тоже.  
Есть ли решение этой ситуации? может быть через формулу columns или index?  
Единственное, что я смогла сделать - это через формулу IF, но мне это не подходит.    
=COUNTIF(IF(F2=1, E1, IF(F2=2, D1:E1, IF(F2=3, C1:E1, IF(F2=4, B1:E1, IF(F2=5, A1:E1, 0))))), "new")  
Буду безмерно благодарна.